Separating equal values
 

Using the large function I can generate a list of high to low values. example:
In column E1 - E4

=LARGE(B1:B4,1)
=LARGE(B1:B4,2)
=LARGE(B1:B4,3)
=LARGE(B1:B4,4)

Next I have the names next to each value:
In column D1- D4

=INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1)

So if my chart was:
A B
Alpha 10
Bravo 2
Charlie 6
Delta 8

The result would be:
D E
Alpha 10
Delta 8
Charlie 6
Bravo 2

Here's my problem. Lets go back to the original chart with new values.
A B
Alpha 10
Bravo 2
Charlie 8
Delta 8

The result would now be:
D E
Alpha 10
Charlie 8
Charlie 8
Bravo 2
Which omits Deltas score.

Is there a way I can tell the index command to, if theres a duplicate value,
ignore the previous result?

Obviously this is a much simpler version than the one I have at present which
contains in excess of 70 names and results.

Separating equal values
 

Put this formula in C1:

=B1+COUNTIF(B$1:B1,B1)/100

and change the formula in D1 to this:

=INDEX(A$1:A$4,MATCH(LARGE(C$1:C$4,ROW(A1)),C$1:C$ 4,FALSE),1)

Then copy both these down to get this:

alpha 10 10.01 alpha 10
bravo 2 2.01 delta 8
charlie 8 8.01 charlie 8
delta 8 8.02 bravo 2

This is effectively giving you a tie-break so that up to 100 ties
could be distinguished, as long as your numbers in column B are
integers. If you want to see charlie appear before bravo in the sorted
list, then change the + in the first formula to a -.

Hope this helps.

Pete

On Jan 21, 11:49*pm, Darren wrote:
Using the large function I can generate a list of high to low values. example:
In column E1 - E4

=LARGE(B1:B4,1)
=LARGE(B1:B4,2)
=LARGE(B1:B4,3)
=LARGE(B1:B4,4)

Next I have the names next to each value:
In column D1- D4

=INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1)

So if my chart was:
* A * * * * * * * *B
Alpha * * * * * 10
Bravo * * * * * *2
Charlie * * * * *6
Delta * * * * * * 8

The result would be:
* *D * * * * * * * *E
Alpha * * * * * *10
Delta * * * * * * *8
Charlie * * * * * 6
Bravo * * * * * * 2

Here's my problem. Lets go back to the original chart with new values.
*A * * * * * * * *B
Alpha * * * * * 10
Bravo * * * * * *2
Charlie * * * * *8
Delta * * * * * * 8

The result would now be:
* *D * * * * * * * *E
Alpha * * * * * *10
Charlie * * * * * 8
Charlie * * * * * 8
Bravo * * * * * * 2
Which omits Deltas score.

Is there a way I can tell the index command to, if theres a duplicate value,
ignore the previous result?

Obviously this is a much simpler version than the one I have at present which
contains in excess of 70 names and results.

Separating equal values
 

I answered a similar question earlier today in the General Questions forum.
See this:

http://www.microsoft.com/communities...7-78901ecd0fa6

--
Biff
Microsoft Excel MVP


"Darren" wrote in message
...
Using the large function I can generate a list of high to low values.
example:
In column E1 - E4

=LARGE(B1:B4,1)
=LARGE(B1:B4,2)
=LARGE(B1:B4,3)
=LARGE(B1:B4,4)

Next I have the names next to each value:
In column D1- D4

=INDEX(A1:A4,MATCH(LARGE(B1:B4,1),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,2),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,3),B1:B4,FALSE),1)
=INDEX(A1:A4,MATCH(LARGE(B1:B4,4),B1:B4,FALSE),1)

So if my chart was:
A B
Alpha 10
Bravo 2
Charlie 6
Delta 8

The result would be:
D E
Alpha 10
Delta 8
Charlie 6
Bravo 2

Here's my problem. Lets go back to the original chart with new values.
A B
Alpha 10
Bravo 2
Charlie 8
Delta 8

The result would now be:
D E
Alpha 10
Charlie 8
Charlie 8
Bravo 2
Which omits Deltas score.

Is there a way I can tell the index command to, if theres a duplicate
value,
ignore the previous result?

Obviously this is a much simpler version than the one I have at present
which
contains in excess of 70 names and results.