Return Top Ranks For Certain Categories in Column



ColA ColB Columnc Col Z Col AA Col Ab
Col Ac
Apples Ship 3 Apples 1
Apples Train
Apples Train 1 2
Apples Tree
Apples Tree 2 3
Apples Ship
Pears Ball 2 4
Apples Pen
Pears Rock 1
Oranges Toy 1
Apples Pen 4
Orange Mack 2
Pears Sack 3

Had data is in the first 3 columns.

By inputting text into cell Z1, I would like to return the rank, category
and name in the Columns AA, AB and AC in order of rank. What formulas do I
use in AA, AB, and AC? Thanks a lot. I know "Max" has something to do with





One relatively easy way to get there ..

Assuming source data starts in row2 down

Put in Z2: =IF(C2="","",C2+ROW()/10^10)
Leave Z1 empty

Put in AA2:
Copy AA2 to AC2. Select Z2:AC2, copy down to cover the max expected extent
of source data, eg down to AC200? Minimize/hide away col Z. Cols AA to AC
will return the required auto-sort of source cols A to C (in ascending order
by the ranks in col C). Lines with tied ranks will be returned in the same
relative order that they appear within the source.


Hi Max, thanks for the help. Not sure I described what I was going for
exactly. Here it is again, slightly different references though. I tried
what you did, and it's not exactly what I'm trying to do but it's very
clever, thanks.

In Column A13:6000 I have different Categories (Apples, Pears, Oranges).

In Column B13:B6000 I have different Sub Categories

In Column C13:6000 I have unique names

In Column AP13:6000 I have ranks ("1" being the best rank)

In Column CZ13:DZ38, I'd like to list the Categories, Subcategories, Names,
and Rank in order best to worst (top 25)

In Cell CZ12, I would like to be able to input text ("Apples") and have
CZ13:DZ38 to list the top 25 for Apples only. If I instead input "Pears" in
CZ12, I'd like to have CZ13:DZ38 list the top 25 for Pears.

Hope that makes sense. Thanks very much for your help!

Regards, Stevec


See whether this gives you the transformations that you seek

Key input will be made in CZ12: Apples
In CY13:
Copy down to CY6000. This is a criteria col, with tiebreaker

Then place in



Select CZ13:CZ15, copy across as far as required, but by at least 25 cols,
to extract the "Top 25" -- to cater for expected ties in the ranks.

Row 13 gives you the ranks in ascending order across
Rows 14 and 15 returns the corresponding extracts for subcats and unique names


Max, that works great! thanks.

One more question... how do I modify your second formula so it ranks in
ascending order moving down the column (similar to your orignal formulas),
instead of across?

I tried modifying your original formula with the exact references that on
the actual spreadsheet, but I obviously did something wrong, and I can't
figure out what it is...

Thanks a lot for your help.



how do I modify your second formula so it ranks in
ascending order moving down the column
(similar to your orignal formulas),
instead of across?

Just change: COLUMNS($A:A)
to this: ROWS($1:1)

in the top cells' formulas





Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question