Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10 Publications and Matching Text Values
Hello,
I am working on an excel document and I need to find out the top 10 publications in column E and then I need to match these top 10 against the service lines in column J. I have over 10000 lines and there are over 40 publications and 4 different service lines. A lot of people have said do the autosort and count, but I would be here all day. Is someone able to help me? -- WitkosRobinson |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10 Publications and Matching Text Values
Hi,
And by top 10 you mean what? The most frequently occuring items, the ones with the largest cost, most circulation, biggest print? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: Hello, I am working on an excel document and I need to find out the top 10 publications in column E and then I need to match these top 10 against the service lines in column J. I have over 10000 lines and there are over 40 publications and 4 different service lines. A lot of people have said do the autosort and count, but I would be here all day. Is someone able to help me? -- WitkosRobinson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10 Publications and Matching Text Values
Hello,
The most frequently occuring items -- WitkosRobinson "Shane Devenshire" wrote: Hi, And by top 10 you mean what? The most frequently occuring items, the ones with the largest cost, most circulation, biggest print? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "kristy_6278" wrote: Hello, I am working on an excel document and I need to find out the top 10 publications in column E and then I need to match these top 10 against the service lines in column J. I have over 10000 lines and there are over 40 publications and 4 different service lines. A lot of people have said do the autosort and count, but I would be here all day. Is someone able to help me? -- WitkosRobinson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10 Publications and Matching Text Values
Hi,
You may try this Assume that your data is in range D6:E18. In D5:E5, there is Name and Description (headings). In cell F5, type Occurrence. In F6, type =COUNTIF($D$6:$D$18,D6) and copy down. Type Occurrence in cell D20. In cell D21, type =MAX(F6:F18). Now go to Data Filter Advanced Filter and in the Actioin section, select, "Copy to another location". In the list range, select D5:F18. In the criteria box, select D20:D21 and in the copy to box, select a blank cell on the worksheet. Now click on OK. This will transfer the data to the new range. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kristy_6278" wrote in message ... Hello, I am working on an excel document and I need to find out the top 10 publications in column E and then I need to match these top 10 against the service lines in column J. I have over 10000 lines and there are over 40 publications and 4 different service lines. A lot of people have said do the autosort and count, but I would be here all day. Is someone able to help me? -- WitkosRobinson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10 Publications and Matching Text Values
Hello Ashish,
Thank you so much! It keeps saying "The extract range has a missing illegal field name" I have a list of publications some repeating more than once in E2-E1388, I then have the service lines repeating more than once in F2-F1388. I then added an occurance column and did =COUNTIF($E$2:$E$1388,E2) and filled down the entire column and got different results. I then typed Occurance under E1388 and place the =MAX(G2:G1388) under that in cell E1390 which gave me 176. I then went to the data filter and selected copy to another location, listed data range E2,F2&G2 down to 1388, then selected the Occurance and Max Formular in E1389 and E1390 and said ok. It just gave me one publication so many times, a few service lines and occurance number 176. I think I've done someone wrong? as its not showing me the top publications against the service lines. -- WitkosRobinson "Ashish Mathur" wrote: Hi, You may try this Assume that your data is in range D6:E18. In D5:E5, there is Name and Description (headings). In cell F5, type Occurrence. In F6, type =COUNTIF($D$6:$D$18,D6) and copy down. Type Occurrence in cell D20. In cell D21, type =MAX(F6:F18). Now go to Data Filter Advanced Filter and in the Actioin section, select, "Copy to another location". In the list range, select D5:F18. In the criteria box, select D20:D21 and in the copy to box, select a blank cell on the worksheet. Now click on OK. This will transfer the data to the new range. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kristy_6278" wrote in message ... Hello, I am working on an excel document and I need to find out the top 10 publications in column E and then I need to match these top 10 against the service lines in column J. I have over 10000 lines and there are over 40 publications and 4 different service lines. A lot of people have said do the autosort and count, but I would be here all day. Is someone able to help me? -- WitkosRobinson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 10 Publications and Matching Text Values
Hi,
Please cross check once again and if it does not work, then please mail me the file at ask(at)ashishmathur(dot)com. Please explain the problem very clearly. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kristy_6278" wrote in message ... Hello Ashish, Thank you so much! It keeps saying "The extract range has a missing illegal field name" I have a list of publications some repeating more than once in E2-E1388, I then have the service lines repeating more than once in F2-F1388. I then added an occurance column and did =COUNTIF($E$2:$E$1388,E2) and filled down the entire column and got different results. I then typed Occurance under E1388 and place the =MAX(G2:G1388) under that in cell E1390 which gave me 176. I then went to the data filter and selected copy to another location, listed data range E2,F2&G2 down to 1388, then selected the Occurance and Max Formular in E1389 and E1390 and said ok. It just gave me one publication so many times, a few service lines and occurance number 176. I think I've done someone wrong? as its not showing me the top publications against the service lines. -- WitkosRobinson "Ashish Mathur" wrote: Hi, You may try this Assume that your data is in range D6:E18. In D5:E5, there is Name and Description (headings). In cell F5, type Occurrence. In F6, type =COUNTIF($D$6:$D$18,D6) and copy down. Type Occurrence in cell D20. In cell D21, type =MAX(F6:F18). Now go to Data Filter Advanced Filter and in the Actioin section, select, "Copy to another location". In the list range, select D5:F18. In the criteria box, select D20:D21 and in the copy to box, select a blank cell on the worksheet. Now click on OK. This will transfer the data to the new range. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kristy_6278" wrote in message ... Hello, I am working on an excel document and I need to find out the top 10 publications in column E and then I need to match these top 10 against the service lines in column J. I have over 10000 lines and there are over 40 publications and 4 different service lines. A lot of people have said do the autosort and count, but I would be here all day. Is someone able to help me? -- WitkosRobinson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non matching text data of the same values | Excel Discussion (Misc queries) | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side | Excel Discussion (Misc queries) | |||
return values from an array based on matching text value | Excel Worksheet Functions | |||
Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions |