Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #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.



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
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Math Experts - Function to compare where salary is compared to others have 800 rows of data Bob Excel Worksheet Functions 2 January 13th 06 03:51 AM
Inserting a new line when external data changes Rental Man Excel Discussion (Misc queries) 0 January 11th 06 08:05 PM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Limiting the range of a lookup function Dorn Excel Worksheet Functions 5 November 8th 05 02:59 AM


All times are GMT +1. The time now is 03:09 PM.

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"