Sorting Duplicate Value Entries

[all4excel]

I am attaching a file with Call-outs which explain the common problems faced during Sorting Duplicate values..
I have two columns with name and Marks with Unsorted data.

When i sort these columns , it works fine but when it encounters duplicate value it gives the same name twice..

How do i overcome this ..

[VBA Noob]

Try this array (Ctrl + Shift + enter)

=INDEX($A$1:$A$16,MATCH(LARGE(($B$1:$B$16-ROW($B$1:$B$16)/10^5),ROWS(C$1:C1)),$B$1:$B$16-ROW($B$1:$B$16)/10^5,0))

VBA Noob

[all4excel]

It really works , however i would apreciate if u could explain in details.

[VBA Noob]

Does this help

ROW($B$1:$B$16)/10^5 returns 0.00001

So when it looks for Mel it returns 1010 - 0.00012 which is 1009.99988 e.g 1010 less row number 12 x 0.00001.

So when it now looks at Cliff it returns 13 x 0.00001 again the row number to make a unique number

So combined this will return the row which the index uses for the return

the ROWS(C$1:C1)) increments by one when you drag down

=MATCH(LARGE(($B$1:$B$16-ROW($B$1:$B$16)/10^5),ROWS(C$1:C1)),$B$1:$B$16-ROW($B$1:$B$16)/10^5,0)

VBA Noob

[all4excel]

This is a good approcah to distinguish between same values by introducing an additional negligible value in the form of the ROW number.