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

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

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

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

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

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