ExcelBanter - Separating equal values

ExcelBanter (https://www.excelbanter.com/)

- Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)

- - Separating equal values (https://www.excelbanter.com/charts-charting-excel/254051-separating-equal-values.html)

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 program to, if theres a duplicate value,
ignore the previous result?

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

Separating equal values

Faced with a similar problem, this was my workaround - may not be clever but
it worked for me.
Assuming the table begins in A1, in C1 enter
=RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)/100
Carefully note the mix of absolute and relative references in the COUNTIF
Copy down the column to give: 4.01, 1.01, 2.01, 2.02
These are what I call 'modified rankings'
Now use your LARGE as before but working on the C helper column

Note that Delta will precede Charlie. To get the same order as the table use
negation before the COUNTIF
=RANK(B1,$B$1:$B$4,1) - COUNTIF($B$1:B1,B1)/100

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"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 program to, if theres a duplicate value,
ignore the previous result?

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

Separating equal values

Thankyou so much Bernard. As for the comment of 'may not be clever', I don't
care how clumsy it looks it does the job.

"Bernard Liengme" wrote:

Faced with a similar problem, this was my workaround - may not be clever but
it worked for me.
Assuming the table begins in A1, in C1 enter
=RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)/100
Carefully note the mix of absolute and relative references in the COUNTIF
Copy down the column to give: 4.01, 1.01, 2.01, 2.02
These are what I call 'modified rankings'
Now use your LARGE as before but working on the C helper column

Note that Delta will precede Charlie. To get the same order as the table use
negation before the COUNTIF
=RANK(B1,$B$1:$B$4,1) - COUNTIF($B$1:B1,B1)/100

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"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 program to, if theres a duplicate value,
ignore the previous result?

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

.

Separating equal values

Thanks for the feedback
Bernard

"Darren" wrote in message
...
Thankyou so much Bernard. As for the comment of 'may not be clever', I
don't
care how clumsy it looks it does the job.

"Bernard Liengme" wrote:

Faced with a similar problem, this was my workaround - may not be clever
but
it worked for me.
Assuming the table begins in A1, in C1 enter
=RANK(B1,$B$1:$B$4,1)+COUNTIF($B$1:B1,B1)/100
Carefully note the mix of absolute and relative references in the COUNTIF
Copy down the column to give: 4.01, 1.01, 2.01, 2.02
These are what I call 'modified rankings'
Now use your LARGE as before but working on the C helper column

Note that Delta will precede Charlie. To get the same order as the table
use
negation before the COUNTIF
=RANK(B1,$B$1:$B$4,1) - COUNTIF($B$1:B1,B1)/100

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"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 program to, if theres a duplicate value,
ignore the previous result?

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

.