Convert list of pairs [a(i),b(i)] to table that shows # of occurrences of each pair

Given something like this:
apple,jan
apple,feb
apple,mar
apple,jan
apple,feb
orange,jan
orange,feb
orange,jan
orange,feb
banana,jan
banana,feb
banana,mar
banana,jan
banana,feb

I'd like to create a table that has three rows (the fruits) and three
columns (the months).  At the intersection of each row and column I'd
like the number of occurrences of that particular fruit-month pair.

Thanks for your time.
Tom Z.
0
tzimoski (4)
8/21/2003 3:12:25 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
623 Views

Similar Articles

[PageSpeed] 10

Tom,

Assume your data is in the range A1:A14, and you have a 
grid starting in cell C1 that looks like:

  C        D   E   F
1          jan feb mar
2 apple
3 orange
4 banana

Enter the following formula in cell D2 and then copy it to 
cells D2:F4:

=COUNTIF($A$1:$A$14,$C2&","&D$1)

Hope this helps,
Ryan

>-----Original Message-----
>Given something like this:
>apple,jan
>apple,feb
>apple,mar
>apple,jan
>apple,feb
>orange,jan
>orange,feb
>orange,jan
>orange,feb
>banana,jan
>banana,feb
>banana,mar
>banana,jan
>banana,feb
>
>I'd like to create a table that has three rows (the 
fruits) and three
>columns (the months).  At the intersection of each row 
and column I'd
>like the number of occurrences of that particular fruit-
month pair.
>
>Thanks for your time.
>Tom Z.
>.
>
0
ryanpoth (3)
8/21/2003 4:15:45 AM
My formula is only limited to three fruits/months by way 
of example. If a larger data set is used, the column and 
row headings can be extended to whatever size is desired 
and it will still function properly.

>-----Original Message-----
>Unless your basic data is limited to 3 fruits and 3 
months (in which 
>case something like Ryan Poth's solution below might work-
-though not 
>for me; it gave me all 0's), then if the functions from 
the downloadable 
>file at http://home.pacbell.net/beban are available to 
your workbook, 
>the following will work for up to 12 months and up to 10 
fruits (and can 
>be expanded to any arbitrary number of fruits with a 
trivial modification:
>
>In C2:C11 array enter =TRANSPOSE(ArrayUniques(A1:A14))
>
>In D1:O1 array enter =ArrayUniques(B1:B14)
>
>In D2 enter and fill to F4 =SUMPRODUCT(($A$1:$A$14=$C2)*
($B$1:$B$14=D$1))
>
>For more fruits, the first formula can be array entered 
into C2:C16 (for 
>15) or C2:C301 (for 300) or whatever.
>
>Alan Beban
>
>Ryan Poth wrote:
>> Tom,
>> 
>> Assume your data is in the range A1:A14, and you have a 
>> grid starting in cell C1 that looks like:
>> 
>>   C        D   E   F
>> 1          jan feb mar
>> 2 apple
>> 3 orange
>> 4 banana
>> 
>> Enter the following formula in cell D2 and then copy it 
to 
>> cells D2:F4:
>> 
>> =COUNTIF($A$1:$A$14,$C2&","&D$1)
>> 
>> Hope this helps,
>> Ryan
>> 
>> 
>>>-----Original Message-----
>>>Given something like this:
>>>apple,jan
>>>apple,feb
>>>apple,mar
>>>apple,jan
>>>apple,feb
>>>orange,jan
>>>orange,feb
>>>orange,jan
>>>orange,feb
>>>banana,jan
>>>banana,feb
>>>banana,mar
>>>banana,jan
>>>banana,feb
>>>
>>>I'd like to create a table that has three rows (the 
>> 
>> fruits) and three
>> 
>>>columns (the months).  At the intersection of each row 
>> 
>> and column I'd
>> 
>>>like the number of occurrences of that particular fruit-
>> 
>> month pair.
>> 
>>>Thanks for your time.
>>>Tom Z.
>>>.
>>>
>> 
>
>.
>
0
ryanpoth (3)
8/21/2003 6:12:19 AM
Understood; although the addition of a single fruit or another month to 
the basic data requires revisiting the setup output range.

But the formula still returns all 0's. That's to be expected because 
it's looking for, e.g., "apple,jan" or "banana,feb" in Column A. No such 
strings appear in Column A.

Alan Beban

Ryan Poth wrote:
> My formula is only limited to three fruits/months by way 
> of example. If a larger data set is used, the column and 
> row headings can be extended to whatever size is desired 
> and it will still function properly.
> 
> 
>>-----Original Message-----
>>Unless your basic data is limited to 3 fruits and 3 
> 
> months (in which 
> 
>>case something like Ryan Poth's solution below might work-
> 
> -though not 
> 
>>for me; it gave me all 0's), then if the functions from 
> 
> the downloadable 
> 
>>file at http://home.pacbell.net/beban are available to 
> 
> your workbook, 
> 
>>the following will work for up to 12 months and up to 10 
> 
> fruits (and can 
> 
>>be expanded to any arbitrary number of fruits with a 
> 
> trivial modification:
> 
>>In C2:C11 array enter =TRANSPOSE(ArrayUniques(A1:A14))
>>
>>In D1:O1 array enter =ArrayUniques(B1:B14)
>>
>>In D2 enter and fill to F4 =SUMPRODUCT(($A$1:$A$14=$C2)*
> 
> ($B$1:$B$14=D$1))
> 
>>For more fruits, the first formula can be array entered 
> 
> into C2:C16 (for 
> 
>>15) or C2:C301 (for 300) or whatever.
>>
>>Alan Beban
>>
>>Ryan Poth wrote:
>>
>>>Tom,
>>>
>>>Assume your data is in the range A1:A14, and you have a 
>>>grid starting in cell C1 that looks like:
>>>
>>>  C        D   E   F
>>>1          jan feb mar
>>>2 apple
>>>3 orange
>>>4 banana
>>>
>>>Enter the following formula in cell D2 and then copy it 
>>
> to 
> 
>>>cells D2:F4:
>>>
>>>=COUNTIF($A$1:$A$14,$C2&","&D$1)
>>>
>>>Hope this helps,
>>>Ryan
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>Given something like this:
>>>>apple,jan
>>>>apple,feb
>>>>apple,mar
>>>>apple,jan
>>>>apple,feb
>>>>orange,jan
>>>>orange,feb
>>>>orange,jan
>>>>orange,feb
>>>>banana,jan
>>>>banana,feb
>>>>banana,mar
>>>>banana,jan
>>>>banana,feb
>>>>
>>>>I'd like to create a table that has three rows (the 
>>>
>>>fruits) and three
>>>
>>>
>>>>columns (the months).  At the intersection of each row 
>>>
>>>and column I'd
>>>
>>>
>>>>like the number of occurrences of that particular fruit-
>>>
>>>month pair.
>>>
>>>
>>>>Thanks for your time.
>>>>Tom Z.
>>>>.
>>>>
>>>
>>.
>>
> 

0
beban (93)
8/21/2003 7:27:56 AM
My apologies, Ryan. On rereading the original post, "apple,jan" and 
"banana,feb" are precisely the strings that the OP indicated were in the 
list. My responses were all directed to fruit in Column A and month in 
Column B.

Sorry,
Alan Beban

Ryan Poth wrote:
> My formula is only limited to three fruits/months by way 
> of example. If a larger data set is used, the column and 
> row headings can be extended to whatever size is desired 
> and it will still function properly.
> 
> 
>>-----Original Message-----
>>Unless your basic data is limited to 3 fruits and 3 
> 
> months (in which 
> 
>>case something like Ryan Poth's solution below might work-
> 
> -though not 
> 
>>for me; it gave me all 0's), then if the functions from 
> 
> the downloadable 
> 
>>file at http://home.pacbell.net/beban are available to 
> 
> your workbook, 
> 
>>the following will work for up to 12 months and up to 10 
> 
> fruits (and can 
> 
>>be expanded to any arbitrary number of fruits with a 
> 
> trivial modification:
> 
>>In C2:C11 array enter =TRANSPOSE(ArrayUniques(A1:A14))
>>
>>In D1:O1 array enter =ArrayUniques(B1:B14)
>>
>>In D2 enter and fill to F4 =SUMPRODUCT(($A$1:$A$14=$C2)*
> 
> ($B$1:$B$14=D$1))
> 
>>For more fruits, the first formula can be array entered 
> 
> into C2:C16 (for 
> 
>>15) or C2:C301 (for 300) or whatever.
>>
>>Alan Beban
>>
>>Ryan Poth wrote:
>>
>>>Tom,
>>>
>>>Assume your data is in the range A1:A14, and you have a 
>>>grid starting in cell C1 that looks like:
>>>
>>>  C        D   E   F
>>>1          jan feb mar
>>>2 apple
>>>3 orange
>>>4 banana
>>>
>>>Enter the following formula in cell D2 and then copy it 
>>
> to 
> 
>>>cells D2:F4:
>>>
>>>=COUNTIF($A$1:$A$14,$C2&","&D$1)
>>>
>>>Hope this helps,
>>>Ryan
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>Given something like this:
>>>>apple,jan
>>>>apple,feb
>>>>apple,mar
>>>>apple,jan
>>>>apple,feb
>>>>orange,jan
>>>>orange,feb
>>>>orange,jan
>>>>orange,feb
>>>>banana,jan
>>>>banana,feb
>>>>banana,mar
>>>>banana,jan
>>>>banana,feb
>>>>
>>>>I'd like to create a table that has three rows (the 
>>>
>>>fruits) and three
>>>
>>>
>>>>columns (the months).  At the intersection of each row 
>>>
>>>and column I'd
>>>
>>>
>>>>like the number of occurrences of that particular fruit-
>>>
>>>month pair.
>>>
>>>
>>>>Thanks for your time.
>>>>Tom Z.
>>>>.
>>>>
>>>
>>.
>>
> 

0
beban (93)
8/21/2003 7:55:32 AM
OK, now that I'm finally on the right track, the following will set up 
the output structure so that it need not be changed if additional fruits 
and months are added to the list:

Array enter into C2:C26 (for up to 25 fruits) 
=ArrayUniques(LEFT(A1:A14,FIND(",",A1:A14)-1),TRUE)

Array enter into D1:O1 =ArrayUniques(RIGHT(A1:A14,3))

Alan Beban

Ryan Poth wrote:
> My formula is only limited to three fruits/months by way 
> of example. If a larger data set is used, the column and 
> row headings can be extended to whatever size is desired 
> and it will still function properly.
> 
> 
>>-----Original Message-----
>>Unless your basic data is limited to 3 fruits and 3 
> 
> months (in which 
> 
>>case something like Ryan Poth's solution below might work-
> 
> -though not 
> 
>>for me; it gave me all 0's), then if the functions from 
> 
> the downloadable 
> 
>>file at http://home.pacbell.net/beban are available to 
> 
> your workbook, 
> 
>>the following will work for up to 12 months and up to 10 
> 
> fruits (and can 
> 
>>be expanded to any arbitrary number of fruits with a 
> 
> trivial modification:
> 
>>In C2:C11 array enter =TRANSPOSE(ArrayUniques(A1:A14))
>>
>>In D1:O1 array enter =ArrayUniques(B1:B14)
>>
>>In D2 enter and fill to F4 =SUMPRODUCT(($A$1:$A$14=$C2)*
> 
> ($B$1:$B$14=D$1))
> 
>>For more fruits, the first formula can be array entered 
> 
> into C2:C16 (for 
> 
>>15) or C2:C301 (for 300) or whatever.
>>
>>Alan Beban
>>
>>Ryan Poth wrote:
>>
>>>Tom,
>>>
>>>Assume your data is in the range A1:A14, and you have a 
>>>grid starting in cell C1 that looks like:
>>>
>>>  C        D   E   F
>>>1          jan feb mar
>>>2 apple
>>>3 orange
>>>4 banana
>>>
>>>Enter the following formula in cell D2 and then copy it 
>>
> to 
> 
>>>cells D2:F4:
>>>
>>>=COUNTIF($A$1:$A$14,$C2&","&D$1)
>>>
>>>Hope this helps,
>>>Ryan
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>Given something like this:
>>>>apple,jan
>>>>apple,feb
>>>>apple,mar
>>>>apple,jan
>>>>apple,feb
>>>>orange,jan
>>>>orange,feb
>>>>orange,jan
>>>>orange,feb
>>>>banana,jan
>>>>banana,feb
>>>>banana,mar
>>>>banana,jan
>>>>banana,feb
>>>>
>>>>I'd like to create a table that has three rows (the 
>>>
>>>fruits) and three
>>>
>>>
>>>>columns (the months).  At the intersection of each row 
>>>
>>>and column I'd
>>>
>>>
>>>>like the number of occurrences of that particular fruit-
>>>
>>>month pair.
>>>
>>>
>>>>Thanks for your time.
>>>>Tom Z.
>>>>.
>>>>
>>>
>>.
>>
> 

0
beban (93)
8/21/2003 9:11:17 AM
"Alan Beban" wrote...
>OK, now that I'm finally on the right track, the following will set up 
>the output structure so that it need not be changed if additional fruits 
>and months are added to the list:
>
>Array enter into C2:C26 (for up to 25 fruits) 
>=ArrayUniques(LEFT(A1:A14,FIND(",",A1:A14)-1),TRUE)
>
>Array enter into D1:O1 =ArrayUniques(RIGHT(A1:A14,3))

Or to avoid the need for slow udfs (not a dig at yours, all udfs are slow), use
the following. First, defined names.

Name          RefersTo
----          --------
Lst           =Sheet1!$A$1:$A$14  (the original list)
Lst_Fld1      =LEFT(Lst,FIND(",",Lst)-1)
Lst_Fld2      =MID(Lst,FIND(",",Lst)+1,1024)

Then formulas.

C2:
=INDEX(Lst_Fld1,1)

C3 [array formula]:
=INDEX(Lst_Fld1,MATCH(0,COUNTIF(C$2:C2,Lst_Fld1),0))

D1:
=INDEX(Lst_Fld2,1)

E1 [array formula]:
=INDEX(Lst_Fld2,MATCH(0,COUNTIF($D1:D1,Lst_Fld2),0))

Fill C3 down as far as needed and E1 right as far as needed. This approach has
the advantage that the OP doesn't need to know the number of distinct row and
column labels ahead of time so as to select large enough ranges into which to
enter multiple cell array formulas.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
0
hrlngrv (1990)
8/21/2003 10:09:11 PM
Reply:

Similar Artilces:

Drop-down Lists
I created a drop-down list...works fine. Now I'm looking to create a feature whenever one of the items is selected (from the drop-down list) that data from another sheet (same workbook) is displayed. Thanks. It depends how your data is organised, but you could use VLOOKUP( ) to return data from another sheet - you might need several formulae across a row to return several cells of data, each dependent on your choice from the drop-down list. Hope this helps - if you need further help please re-post with some further details. Pete ...

Comparing two tables
hey just a short question :-D I have two tables, one with a list with 1330 entries and one with 720 entries, the 720 are all in the big list as well, is there a way to write the 610 entries from the big list that arent in the small list into another excel table without doing it one by one ? thanks in advance Peter PEter, try to use the "VLOOKUP" function...it will help you to identify with values are in both tables (sheets)... so you can mark the values that are in both tables and separete then easily... i don´t know if you know the VLOOKUP function...if you don´...

how do I enter a list in a list box or a combo box
hi i am trying to enter a list of names into either a list box or a combo box but i can not find out how to do it... can anybody help please... On 31/05/2010 11:42 AM, word challenged wrote: > hi i am trying to enter a list of names into either a list box or a combo box > but i can not find out how to do it... can anybody help please... See the following page of Greg Maxey's website: http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm If it's an ActiveX combobox that you haved inserted directly into the document, right click on it and select View Code and use c...

converting PDF to Excel Sheet?
Hello, I would like to convert a PDF to Excel using a simple linux command line tool. Anyone know if that is possible? The tool coul also be a windows tool, but I am looking for a tool that is completel automated, with no user intevention. The reason is that I have a program that downloads PDFs every hour o so, and I would like to convert that pdf to an excel sheet so i coul grab data off it easily. Or maybe, does anyone know of an easier way to grab tables informatio off PDFS? thank -- Message posted from http://www.ExcelForum.com Take a look at PDF and Excel http://www.mvps...

Convert Word 6.0 to Word 2003 programatically
With the latest security update from MS Security Existing word templates documents could not be loaded and they started throwing exceptions each time we load. We would like to convert the existing Word 6.0 to Word 2003 programmatically or by using a tool. How do we convert using .NET? Or any existing MS Tool Thanks for the help It sounds like your problem maybe with the KB973904 security update. Try removing it. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, ...

pcnetsecurity@gmail.com =?UTF-8?B?QXNzaXN0w6puY2lhIFTDqWM=?= =?UTF-8?B?bmljYSAgbWFudXRlbsOnw6M=?= =?UTF-8?B?byBkZSBjb21wdXRhZG9y?= =?UTF-8?B?ZXMgaW5mb3JtYXRpY2Eg?= =?UTF-8?B?Vml0w7NyaWEtZXMgOTEwMTA=?=
Contato: pcnetsecurity@gmail.com Contato: pcnetsecurity @ gmail.com Planos a partir de R$ 250,00 . Assist�ncia T�cnica Prestamos assist�ncia t�cnica nos computadores de sua empresa ou resid�ncia, e tamb�m possu�mos uma equipe qualificada para fazer a manuten��o no pr�prio local. - Contratos de Suporte e Manuten��o Reduza os custos de sua empresa com solicita��es de visitas t�cnicas para seus computadores, elaboramos um contrato de manuten��o integrado para sua empresa onde disponibilizamos: t�cnicos, equipamentos de suporte e substitui��o, e atendimento no hor�rio comercial ou ...

CImage::AlphaBlend and converting to 32-bit bitmap
Hi, I'm using the CImage::AlphaBlend function in my program. This works well, but only if the bitmap attached to the CImage object is already in 32 bit format. Otherwise AlphaBlend does nothing. I'm having a hard time figuring out how to convert other bitmap formats to 32 bit. Alternatively, is there a way to access the bitmap directly? There is the CImage::GetBits function. How do you use the array that's returned to manipulate the bitmap? (How do you treat different bitmap formats?) Any suggestions would be greatly appreciated! Lena ...

Username not in Global Address List
Hi all, I have added several user accounts on my Exchange 2003 server. All of them would show up in the Glabal Address List (GAL) except the last user I added. I am going to called it UserX. This last user account would show up in all the users' workstations except one station. Let's call it StationY. This particular client at StationY would see all the users in the Global Address List except UserX. In other words, UserX doesn't show up in the GAL on StationY. What should I do in order to bring UserX to show up in GAL on StationY? I appreciate any help on this. Thanks, ...

FE-BE Server Distribution Lists
Hello, I have a front end 2003 exchange server and a back end 2003 exchange server. I have an SMTP virtual server accepting in mail, and the SMTP connector configured with the FE VS as the bridgehead. My problem is when people send to global distribution lists from outside of the organisation they sit in the FE SMTP Mailbox and cause a x.400 message loop.. I have managed to research this problem to discover if I go in and manually set the distribution group expansion server to the BE, then people sending to the distribution group works fine, otherwise doesnt work. As mentioned this is a p...

Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...

Chart does not show up in print preview and wont print?
I have chart that is a workbook page but when I select print preview it does not shown up, only a blank pages comes up. And so it wont print. How can I fix this? I have Office 2007 and I can not select set print area or clear print area, they are shaded out. ...

My WMP library contains wav files best way to convert to mp3
I'm loading my Vinyl albums into WMP as WAV files. I've completed 100 and now have a very large file (48G). How can I convert the WAV files to MP3 without having to recreate my WMP library. i've split all the tracks and renamed them and added art work. I've converted the wav files to MP3 but when I drag the converted files into my WPM library I get a red X next to track that is associated with that file. If I do nothing will WMP automatically convert the files when I sync a device to the WMP? WMP supports transcoding When syncing, the files will be translate...

Nothing shows up in Database objects in Database Wizard
Hello, I am having touble with Visio 2003 once again. Though the db wizard, I created an excel spreadsheet (table) for data and am tryinig to link the chart back to this original database so that when I change something in the database it will change correspondingly. I am going back through the DB wizard, and can only get as far as selecting a database object to connect to. I have selected the original dada source, checked table, but nothing showes up under the database objects. Am I or my computer missing something? Please help! Thank You. Have you defined a name for the region ...

exporting a mailing list
I would like to export a clients customer file into excell so they can send out a mailer. This seams like it should be an easy task. I have tried to run a report, customer list but it doesnt give me all the info I need. (address, city, state). Is there a place to export this info. Im probally overlooking something stupid but I dont see it. "Zack Anderson" <zack@electronicbiz.com> ha scritto nel messaggio news:26049526-2FBB-4CA2-8B20-CBD89EE38E29@microsoft.com... >I would like to export a clients customer file into excell so they can send > out a mailer. This ...

Handling the System Image List ?
Hi guys, In my program I made a Shell Namespace tree viewer that needs the system imagelist which I handle like this: // let's get the image list SHFILEINFO sfi; ZeroMemory(&sfi,sizeof(sfi)); HIMAGELIST hImageList=(HIMAGELIST)SHGetFileInfo(_T("C:\\"),0,&sfi,sizeof(SHFILEINFO),SH GFI_SYSICONINDEX|SHGFI_SMALLICON); // attach it to my class member m_imageList.Attach(hImageList); // later on in my destructor I free it m_imageList.Detach(); I think I am doing it right because it works. The thing is, I want to use that tree I made in different spots but it seems I can'...

lookup list
I'd like to have a lookup list, which shows 2 colums of the reference table. The columns are next to each other. Is that possible? ...

Converting Access to MS SQL
I got a big Access file (1 400 tables) to convert to SQL and I would > like to be provided with some automated tools, except upsizing wizard > and DTS, to convert it on my own. > > I got a lot of forms and query to convert too. > > I analysed a tool suggested by Douglas last week, but it convert only the data, and not the froms and query. Can someday provide me with other tool name ? > > Thanks, > > Guy As I told you before, SQL Server doesn't have forms and queries in it: it's a database only. Therefore it's impossible to find a product that will co...

Year series shows wrong in chart
Hi all : using Excel 2003, I have what should not even be a problem that I cannot solve. I have a series of years in a column, 1970-2006 to be exact, and these are the labels for my x axis. When I insert chart this appears as 1,2,3,...instead of 1970, 1971, etc. It doesn't matter how I format that column, text, general, custom, nothing works. Why is it not just giving me the data in the format that is actually there and how can I get it? thanks for any help Bevin B. Try first entering the dates as dates. To check to make sure they're entered as dates, after entering the data c...

Ole objects in records/tables
I have a question every time I tried to go to word and powerpoint and says type a prodect key I typed from the back of my labtop but it keeps saying it wrong.So what do I do? WHAT??????? "Kevin Ramos" <kevin's email> wrote in message news:%23X$8BdeQIHA.2268@TK2MSFTNGP02.phx.gbl... >I have a question every time I tried to go to word and powerpoint and says >type a prodect key I typed from the back of my labtop but it keeps saying >it wrong.So what do I do? ...

Maximizing dialog box and Control list
Hi, I programming using VS 6.0, MFC SDI. I have a dialog box, wich contains a CListCtrl table. My problem is that, when the dialog box is maximized, the control lists and the ok, cancel, and other buttos stay the way their are. I mean, they don't change it size accordingly. How can I maximize or change the dialog box size and everything on it change accordingly? I hope I've made myself clear. Thanks in advance Hi, You should override OnSize method of your dialog box and use SetWindowPos for your child windows. f.e. void CMyDialog::OnSize(UINT nType, int cx, int cy) { CDialog::...

"Recently Used File List"
Using Excel 2002... Is there any way to remove files from this list other than waiting for them to be replaced over time? You could remove then. Here is code showing how to get at it Dim i As Long For i = 0 To Application.RecentFiles.Count i = i + 1 If Application.RecentFiles(i).Name = "ABC.xls" Then Application.RecentFiles(i).Delete End If Next i But don't forget, some items in the list will be just the file name, some will include the path -- HTH RP (remove nothere from the email address if mailing direct) "phil6666&qu...

Could not delete from specified tables
I created the following query but I get the "Could Not Delete From Specified Tables" error. Although I have access to the table and can delete from that table manually. my query is as follows: DELETE DISTINCTROW tblSatAdv.* FROM tblSatAdv INNER JOIN ( SELECT Max(tblSatAdv.SnapshotInvDte) AS MaxOfSnapshotInvDte FROM tblSatAdv ) AS MAXDTE ON tblSatAdv.SnapshotInvDte = MAXDTE.MaxOfSnapshotInvDte; It is not possible to delete using a aggregate sub Query as a joined "table" Pieter "ISUTri" <GraberJ@gmail.com> wrote in message news:ce6eaad7-6db5-4f03-...

Convert foxpro to access
Does anyone know how to convert a foxpro database to an access database complete with the forms and other objects in the foxpro database? AFAIK, it can't be done. All you'll be able to do is get the data out of FoxPro, but nothing else. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "pointer" <smutuwa@gmail.com> wrote in message news:1184002011.528781.263990@d30g2000prg.googlegroups.com... > Does anyone know how to convert a foxpro database to an access > database complete with the forms and other objects in the fo...

Pivot Table #43
One of the row headings in my pivot Table is 'Ticker'. On the data page there are about 12 rows with a ticker of 'GS'. These willl NOT show up on the pivot table. If I change them to something else (GS_TEST,GS_TEMP, etc) and refresh the pivot table, they appear. When I try to change the ticker back to GS and refresh, they disappear again.. Thoughts?? I've been all over the knowledge base.. Looked up smart tags, checked my autocorrect entries, etc.. I don't know of anything that would cause the problem. If you copy the source data into a new workbook, and crea...

Look up wizard in tables
I have designed a table and in one field of the table I have used 'look up wizard' to make a relationship with another table in the database. When I open the table in datasheet view and click on the field with the 'Look up relationship' it pulls information from the first column only, not the second. The relationship of the tables are right, not sure why it is pulling information from one column only. I would like it to pull from both columns as the row information is related. IMO, you should never ever use lookup fields in table designs. http://www.mvps.org/acc...