Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Rank Data on Occurrence 1

Status
Not open for further replies.

eggohead

Technical User
Oct 19, 2003
25
US
Hello,

I have a huge spreadhseet of data that details sites and work orders. I need to list the top 5 worst sites (sites with the mmost work orders). Whats the best way to attack this and what formulas should I use?

I thought using countif to count the occurence of each site number...but since one site can have many work orders I have duplicate data for many sites.

So the main questions:

1. What do I need to do in the source spreadhsheet with the work orders...extra columns to count etc and what formulas do I use to aggregate that data?

2. What formulas or methods do I need to use in summary spreadsheet that will detail the top sites (unique) with the most work orders?

Hope I explained that OK. Im assuming this can probably with a pivot table. If this is your recommendation please explain how to set it up to only show the top 5 sites with the most frequency in the data set. Tried but couldnt get that to show the way id like it....just a newbie with pivots

Thanks for your help




 



hi,

I'd use MS Query, Advanced Filter or the PivotTable Wizard to get unique occurences of the required data.

Then it seems like COUNTIF would work.

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top