| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
dynamically sorted list with duplicates
Howdy,
I need to create a sorted list from tables of values--not too exciting--but I'm having trouble handling arbitrary number of duplicates. Working with Excel 2003. In a single workbook, I have sheet 1: Name Rank Sex Alice 3 Girl Betty 5 Girl Cathy 3 Girl Donna 1 Girl And sheet 2: Name Rank Sex Adam 3 Boy Bruce 5 Boy Carl 0 Boy David 2 Boy Eric 6 Boy Frank 1 Boy On sheet 3 I want, by rank highest to lowest: Eric 6 Boy Betty 5 Girl Buce 5 Boy Alice 3 Girl Cathy 3 Girl Adam 3 Boy David 2 Boy Donna 1 Girl Frank 1 Boy Carl 0 Boy The order of names of the same rank is not an issue (Betty and Bruce vs Bruce and Betty) but I do need all the names. I know all the names will be unique, but I don't know how many duplicates in rank there will be. What I have right now is: Eric 6 Boy Betty 5 Girl Betty 5 Girl Alice 3 Girl Alice 3 Girl Alice 3 Girl David 2 Boy Donna 1 Girl Donna 1 Girl Carl 0 Boy What I'm doing is first create a single table on sheet 3: Column A: =IF(B1="","",INDEX(Sheet1!A$1:A$10,MATCH(B1,Sheet1 !B$1:B $10,0))) Column B: =IF(ISERROR(LARGE(Sheet1!B$1:B$10,ROW(A1))),"",LAR GE(Sheet1!B $1:B$10,ROW(A1))) Column C: =IF(B1="","",INDEX(Sheet1!C$1:C$10,MATCH(B1,Sheet1 !B$1:B $10,0))) for all the rows with data on sheet 1, and then rows for all the data on sheet 2, with "sheet2" in place of "sheet1" in the formulas. A1 and B1 for the first row. Values are A2 and B2 for the second row, and so on. Then I sort the consollidated list: Column G :=IF(H1="","",INDEX(A$1:A$20,MATCH(H1,B$1:B$20,0)) ) Column H: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B $1:B$20,ROW(A1))) Column I: =IF(H1="","",INDEX(C$1:C$20,MATCH(H1,B$1:B$20,0))) I played around with having the formula in column A check if a cell has the same value as the cell above, but that doesn't really address the issue if there is more than 2 names with the same rank. I tried making a seperate table with ranks and multiplicities: Column M: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B $1:B$20,ROW(A1))) Column N(first row): 1 Column N(rest): =IF(AND(M2=M1,M2<""),N1+1,1) which gives me this: 6 1 5 1 5 2 3 1 3 2 3 3 2 1 1 1 1 2 0 1 So I know for the third entry, this is the second occurance of rank 5. How do I find the index of that second occurance? Is there a way to tell the MATCH and INDEX formulas for column I to search from the first occurance to B$20 and C$20, rather than always from B$1:B$20? Thanks, Sean |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
dynamically sorted list with duplicates
Hi,
May I request you to mail me the workbook and explain the problem very clearly. -- Regards, Ashish Mathur www.ashishmathur.com http://www.linkedin.com/in/excelenthusiasts wrote in message ... Howdy, I need to create a sorted list from tables of values--not too exciting--but I'm having trouble handling arbitrary number of duplicates. Working with Excel 2003. In a single workbook, I have sheet 1: Name Rank Sex Alice 3 Girl Betty 5 Girl Cathy 3 Girl Donna 1 Girl And sheet 2: Name Rank Sex Adam 3 Boy Bruce 5 Boy Carl 0 Boy David 2 Boy Eric 6 Boy Frank 1 Boy On sheet 3 I want, by rank highest to lowest: Eric 6 Boy Betty 5 Girl Buce 5 Boy Alice 3 Girl Cathy 3 Girl Adam 3 Boy David 2 Boy Donna 1 Girl Frank 1 Boy Carl 0 Boy The order of names of the same rank is not an issue (Betty and Bruce vs Bruce and Betty) but I do need all the names. I know all the names will be unique, but I don't know how many duplicates in rank there will be. What I have right now is: Eric 6 Boy Betty 5 Girl Betty 5 Girl Alice 3 Girl Alice 3 Girl Alice 3 Girl David 2 Boy Donna 1 Girl Donna 1 Girl Carl 0 Boy What I'm doing is first create a single table on sheet 3: Column A: =IF(B1="","",INDEX(Sheet1!A$1:A$10,MATCH(B1,Sheet1 !B$1:B $10,0))) Column B: =IF(ISERROR(LARGE(Sheet1!B$1:B$10,ROW(A1))),"",LAR GE(Sheet1!B $1:B$10,ROW(A1))) Column C: =IF(B1="","",INDEX(Sheet1!C$1:C$10,MATCH(B1,Sheet1 !B$1:B $10,0))) for all the rows with data on sheet 1, and then rows for all the data on sheet 2, with "sheet2" in place of "sheet1" in the formulas. A1 and B1 for the first row. Values are A2 and B2 for the second row, and so on. Then I sort the consollidated list: Column G :=IF(H1="","",INDEX(A$1:A$20,MATCH(H1,B$1:B$20,0)) ) Column H: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B $1:B$20,ROW(A1))) Column I: =IF(H1="","",INDEX(C$1:C$20,MATCH(H1,B$1:B$20,0))) I played around with having the formula in column A check if a cell has the same value as the cell above, but that doesn't really address the issue if there is more than 2 names with the same rank. I tried making a seperate table with ranks and multiplicities: Column M: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B $1:B$20,ROW(A1))) Column N(first row): 1 Column N(rest): =IF(AND(M2=M1,M2<""),N1+1,1) which gives me this: 6 1 5 1 5 2 3 1 3 2 3 3 2 1 1 1 1 2 0 1 So I know for the third entry, this is the second occurance of rank 5. How do I find the index of that second occurance? Is there a way to tell the MATCH and INDEX formulas for column I to search from the first occurance to B$20 and C$20, rather than always from B$1:B$20? Thanks, Sean |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
dynamically sorted list with duplicates
One alternative formulas set-up
to retrieve it dynamically in sorted descending order by rank in Sheet3 Illustrated in this sample: http://www.freefilehosting.net/download/3g9md Full dynamic sorted list from 2 shts w ties.xls In Sheet3 In A1: =IF(Sheet1!B2="","",ROW()) In B1: =IF(Sheet2!B2="","",ROW()) In C1: =IF(ROW()COUNT($A:$A),IF(ROW()-MAX($A:$A)COUNT($B:$B),"",INDEX(Sheet2!A:A,SMALL( $B:$B,ROW()-MAX($A:$A))+1)),INDEX(Sheet1!A:A,SMALL($A:$A,ROW() )+1)) Copy C1 to E1 In F1: =IF(D1="","",D1-ROW()/10^10) Select A1:F1, copy down to cover the max expected extents of the combined data in both Sheet1 and Sheet2, say down to F20 Then place In G1: =IF(H2="","",ROWS($1:1)) In H1: =IF(ROWS($1:1)COUNT($F:$F),"",INDEX(C:C,MATCH(LAR GE($F:$F,ROWS($1:1)),$F:$F,0))) Copy H1 to J1. Select G1:J1, fill down to J21 (one row beyond the extent filled in cols A to F). Hide away cols A to F. Cols H to J will return the desired full combined results (inclusive of lines with tied ranks), sorted in descending order by the ranks. Col G provides the auto-numbering for the results set. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ... Howdy, I need to create a sorted list from tables of values--not too exciting--but I'm having trouble handling arbitrary number of duplicates. Working with Excel 2003. In a single workbook, I have sheet 1: Name Rank Sex Alice 3 Girl Betty 5 Girl Cathy 3 Girl Donna 1 Girl And sheet 2: Name Rank Sex Adam 3 Boy Bruce 5 Boy Carl 0 Boy David 2 Boy Eric 6 Boy Frank 1 Boy On sheet 3 I want, by rank highest to lowest: Eric 6 Boy Betty 5 Girl Buce 5 Boy Alice 3 Girl Cathy 3 Girl Adam 3 Boy David 2 Boy Donna 1 Girl Frank 1 Boy Carl 0 Boy The order of names of the same rank is not an issue (Betty and Bruce vs Bruce and Betty) but I do need all the names. I know all the names will be unique, but I don't know how many duplicates in rank there will be. What I have right now is: Eric 6 Boy Betty 5 Girl Betty 5 Girl Alice 3 Girl Alice 3 Girl Alice 3 Girl David 2 Boy Donna 1 Girl Donna 1 Girl Carl 0 Boy What I'm doing is first create a single table on sheet 3: Column A: =IF(B1="","",INDEX(Sheet1!A$1:A$10,MATCH(B1,Sheet1 !B$1:B $10,0))) Column B: =IF(ISERROR(LARGE(Sheet1!B$1:B$10,ROW(A1))),"",LAR GE(Sheet1!B $1:B$10,ROW(A1))) Column C: =IF(B1="","",INDEX(Sheet1!C$1:C$10,MATCH(B1,Sheet1 !B$1:B $10,0))) for all the rows with data on sheet 1, and then rows for all the data on sheet 2, with "sheet2" in place of "sheet1" in the formulas. A1 and B1 for the first row. Values are A2 and B2 for the second row, and so on. Then I sort the consollidated list: Column G :=IF(H1="","",INDEX(A$1:A$20,MATCH(H1,B$1:B$20,0)) ) Column H: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B $1:B$20,ROW(A1))) Column I: =IF(H1="","",INDEX(C$1:C$20,MATCH(H1,B$1:B$20,0))) I played around with having the formula in column A check if a cell has the same value as the cell above, but that doesn't really address the issue if there is more than 2 names with the same rank. I tried making a seperate table with ranks and multiplicities: Column M: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B $1:B$20,ROW(A1))) Column N(first row): 1 Column N(rest): =IF(AND(M2=M1,M2<""),N1+1,1) which gives me this: 6 1 5 1 5 2 3 1 3 2 3 3 2 1 1 1 1 2 0 1 So I know for the third entry, this is the second occurance of rank 5. How do I find the index of that second occurance? Is there a way to tell the MATCH and INDEX formulas for column I to search from the first occurance to B$20 and C$20, rather than always from B$1:B$20? Thanks, Sean |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
dynamically sorted list with duplicates
You'd get the required sorted list returned in Sheet3 below:
Name Rank Sex Eric 6 Boy Betty 5 Girl Bruce 5 Boy Alice 3 Girl Cathy 3 Girl Adam 3 Boy David 2 Boy Donna 1 Girl Frank 1 Boy Carl 0 Boy -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
dynamically sorted list with duplicates
On Apr 29, 4:24*am, "Max" wrote:
One alternative formulas set-up to retrieve it dynamically in sorted descending order by rank in Sheet3 Illustrated in this sample:http://www.freefilehosting.net/download/3g9md Full dynamic sorted list from 2 shts w ties.xls In Sheet3 In A1: =IF(Sheet1!B2="","",ROW()) In B1: =IF(Sheet2!B2="","",ROW()) In C1: =IF(ROW()COUNT($A:$A),IF(ROW()-MAX($A:$A)COUNT($B:$B),"",INDEX(Sheet2!A:A*,SMALL ($B:$B,ROW()-MAX($A:$A))+1)),INDEX(Sheet1!A:A,SMALL($A:$A,ROW() )+1)) Copy C1 to E1 In F1: =IF(D1="","",D1-ROW()/10^10) Select A1:F1, copy down to cover the max expected extents of the combined data in both Sheet1 and Sheet2, say down to F20 Max, Thank you very much. Clever solution to the issue of repeated rankings. And I did not know a range of cells could be specified by column--$A:A$ as opposed to $A1:$A20. All very useful. Terrific. Ashish Mathur, Thank you for the offer, but I'm all set thanks to Max. Thanks, Sean |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
dynamically sorted list with duplicates
Welcome, Sean.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote Max, Thank you very much. Clever solution to the issue of repeated rankings. And I did not know a range of cells could be specified by column--$A:A$ as opposed to $A1:$A20. All very useful. Terrific. Ashish Mathur, Thank you for the offer, but I'm all set thanks to Max. Thanks, Sean |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation (list) that can be sorted | Excel Discussion (Misc queries) | |||
| lookup in non sorted list | Excel Worksheet Functions | |||
| sorted one list based on another one | Excel Discussion (Misc queries) | |||
| Sorted list | Excel Discussion (Misc queries) | |||
| Need sorted validation list | Excel Worksheet Functions | |||
Linear Mode
