Top 10 Publications and Matching Text Values

  • Thread starter Thread starter kristy_6278
  • Start date Start date
K

kristy_6278

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?
 
Hi,

And by top 10 you mean what? The most frequently occuring items, the ones
with the largest cost, most circulation, biggest print?
 
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
 
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.
 
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 said:
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.
Click to expand...
 
Back
Top