Top 10 Publications and Matching Text Values

[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?

 

[Shane Devenshire]

Hi,

And by top 10 you mean what? The most frequently occuring items, the ones
with the largest cost, most circulation, biggest print?

 

[kristy_6278]

Hello,

The most frequently occuring items

 

[Ashish Mathur]

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 D2021 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]

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.

 

[Ashish Mathur]

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

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.