| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 it. Stevec |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
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(SMA LL($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
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 "Max" wrote: 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(SMA LL($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
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$60 00,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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
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" wrote: 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$60 00,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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
Works great, thanks!
"Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return Top Ranks For Certain Categories in Column
welcome, Steve
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "SteveC" wrote in message ... Works great, thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| column chart with 3 categories | Charts and Charting in Excel | |||
| Create Stacked column of multiple values across categories | Charts and Charting in Excel | |||
| Column Chart - X axis too thin because of MANY categories. | Charts and Charting in Excel | |||
| How to create column of ranks. | Excel Discussion (Misc queries) | |||
| how do I sort a column with several categories (ie red and blue) | Excel Worksheet Functions | |||
Linear Mode
