View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SteveC
 
Posts: n/a
Default Filtering Data with Rank Function

I have about 2000 rows of data.

Column A contains the Data Set Label.
Columns J, K and L contain the data that needs to be filtered (screened)

For all the names in a particular Data Set Label, I would like a screen to
return only those Labels where the data in Column J, K and L are in the top
50%.

Settling odd number of Data Labels this way: If there are 5 data lables, it
retains the top 2 and discards the bottom 3.

For example, looking at the Apple Lables below, such a screen would only
return:

Apples 2 5 4

Because it is the only Data set where the data in Column J, K and L are all
in the top 50% of all categories for Apples.

Column A ... Column J Column K Column L
Apples 3 2 3
Apples 2 5 4
Apples 1 4 4
Apples 1 3 3
Apples 1 4 3
Oranges etc...
Oranges
Oranges
Pears
Pears
Pears
Pears

Using words instead of numbers may be easier to explain:
I get:
Apples Yes Yes Yes

From the data below:

Column A ... Column J Column K Column L
Apples Yes No No
Apples Yes Yes Yes
Apples No Yes Yes
Apples No No No
Apples No Yes No

The Yes assume that the data has the highest 50% numerical value.

In addition, I would like flexibility to screen for the top 10%for each Data
Label, or 20% etc...

I'm thinking this doens't have to be a macro, but maybe I'm wrong? I rather
stay away from a macro if possible... but if not...

Thanks for your help.