Question about LARGE duplicate values.

Hi, I'm kinda new to excel still so could anyone help me with this?
I have 2 columns, one with text and another with numbers. Like this:
A B
a 1
b 2
c 3
d 5
f 5
g 7

I am trying to figure out how to get the largest value, 2nd largest, 3rd etc.
So far I have figured out this, =INDEX(A4:A9; MATCH(LARGE(B4:B9;1);B4:B9;0)) <-- Works well as long as there are no duplicates.
But when there comes a duplicate it only shows 1 of the values.
How could I make them like unique(?) or that it shows them both in a alphabetic order or something similar?
Like that I could have 2 cells, one with "d" and the other one with "f",
Or a cell which would contain both of them.

Thanks for your reply,
Well some progress I did get, I made that function on
a different column and it made new values with commas to make them unique and such.
But when I tried inserting those cells into the function it only showed a #NUM! error.
Do I have to change the index/match function so it understands decimals or did I do something wrong?
A--B-------------C---------------C results
a 1 =B1&COUNTIF($B$1:B1;B1)/100 10,01
b 2 =B2&COUNTIF($B$1:B2;B2)/100 20,01
c 3 =B3&COUNTIF($B$1:B3;B3)/100 30,01
d 5 =B4&COUNTIF($B$1:B4;B4)/100 50,01
f 5 =B5&COUNTIF($B$1:B5;B5)/100 50,02
g 7 =B6&COUNTIF($B$1:B6;B6)/100 70,02

=INDEX(A1:A6; MATCH(LARGE(C1:C6;2);C1:C6;0))
Was the function I used to see the 2nd largest number.
It's exactly the same as last time just that it uses C rather than B.

=INDEX(A$4:A$9,MATCH(LARGE($B$4:$B$9-ROW($4:$9)%,ROW(1:1)),$B$4:$B$9-ROW($4:$9)%,))

combination of CTRL,SHIFT,ENTER (not just ENTER).

Reply to post#1
Here's yet another formulas play to extract the full descending sort ....
Assuming your source data as posted is in cols A and B, from row 2 down
Place in
D2: =IF(B2="","",B2-ROW()/10^5)
E2: =INDEX(A:A,MATCH(LARGE($D:$D,ROWS($1:1)),$D:$D,0))
Copy E2 to F2. Then select D2:F2, copy down to F7
Hide away / minimize col D. Cols E and F will return the full descending sort of cols A and B (ordered by col B values)
Ties if any, will be returned in the same relative order as per source data

p/s: To do likewise for a Full Ascending sort (including ties)
do the same steps as above, but with these replacement formulae
D2: =IF(B2="","",B2+ROW()/10^5)
E2: =INDEX(A:A,MATCH(SMALL($D:$D,ROWS($1:1)),$D:$D,0))
-------------------------------
Success? Wave it, click on the little star at the bottom left of my responses

I got it working, thanks for all the help

Originally Posted by FDibbins

Hi and welcome to the forum

I know there are other ways to break tie-breaks/duplicates, but This is the trick I always use.

If B is where the duplicates would be, in a helper column, copied down...

=B1&countif($B$1:B1,B1)/100
This will give a new/helper column with unique values for B that you can then use in the INDEX/MATCH

Hi, I'm finding this is either giving my scores a second decimal place, or turning 11.00 into 110.01

What would you suggest in this instance? Scores are in Column N and go from row 4 to 18,259.

I'm ranking the scores and the duplicates are giving me incorrect results in other areas.