Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Non matching text data of the same values Mike Excel Discussion (Misc queries) 3 October 6th 08 08:00 PM
Finding Most Recent Values in Col1 -- Summing Matching Values Rothman Excel Discussion (Misc queries) 5 December 20th 07 09:19 PM
Help with Matching Text Fields - Then Moving the Matching Cells Side by Side [email protected] Excel Discussion (Misc queries) 2 June 11th 07 02:38 PM
return values from an array based on matching text value WFBJoeB Excel Worksheet Functions 10 May 14th 07 07:57 PM
Only text values matching using index/match lookup - data type pro Stanton Excel Worksheet Functions 1 August 2nd 05 01:05 AM


All times are GMT +1. The time now is 06:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"