Return Top Ranks For Certain Categories in Column

[SteveC]

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
it.

Stevec

 

[Max]

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:
=IF(ROWS($1:1)>COUNT($Z:$Z),"",INDEX(A:A,MATCH(SMALL($Z:$Z,ROWS($1:1)),$Z:$Z,0)))
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.

 

[SteveC]

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 CZ13Z38, 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
CZ13Z38 to list the top 25 for Apples only. If I instead input "Pears" in
CZ12, I'd like to have CZ13Z38 list the top 25 for Pears.

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

Regards, Stevec

 

[Max]

See whether this gives you the transformations that you seek

Key input will be made in CZ12: Apples
In CY13:
=IF($CZ$12="","",IF(A13=$CZ$12,AP13+ROW()/10^10,""))
Copy down to CY6000. This is a criteria col, with tiebreaker

Then place in
CZ13:
=IF(ISERROR(SMALL($CY$13:$CY$6000,COLUMNS($A:A))),"",INDEX($AP$13:$AP$6000,MATCH(SMALL($CY$13:$CY$6000,COLUMNS($A:A)),$CY$13:$CY$6000,0)))

CZ14:
=IF(ISERROR(SMALL($CY$13:$CY$6000,COLUMNS($A:A))),"",INDEX($B$13:$B$6000,MATCH(SMALL($CY$13:$CY$6000,COLUMNS($A:A)),$CY$13:$CY$6000,0)))

CZ15:
=IF(ISERROR(SMALL($CY$13:$CY$6000,COLUMNS($A:A))),"",INDEX($C$13:$C$6000,MATCH(SMALL($CY$13:$CY$6000,COLUMNS($A:A)),$CY$13:$CY$6000,0)))

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

 

[SteveC]

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.

SteveC

 

[Max]

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

---

 

[SteveC]

Works great, thanks!

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

in the top cells' formulas

 

[Max]

welcome, Steve