Sort table with smallest to largest value with SMALL() and duplicate sorting values

[El_Presidente]

Hello friends,

I would like to ask for your help. :-)

I am trying to sort a table in ascending order with a formula in Excel 2016.
Certainly, I am aware that there are many ways to do this easily without a formula, but in this case I do need the formula-way.

My situation is that I have a column with numerical values (height) an a corresponding column with people's names.
So far my approach is to create a new sorted table next to the original unsorted table, using the height column (sorted with SMALL()) as an anchor for an INDEX(MATCH()) column to add the names next to it.
However, my problem is that two people have the same height and the INDEX(MATCH()) combo only gives me the name of the first person of the duplicate height.

I have attached a workbook to show my problem and would greatly appreciate your inputs! :-)

All the best, stay healthy!

[oeldere]

c3 =B3+Row()/10000000 and drag down (yellow cells).

After that you can use the orignal formula

see the green cells in the attached file.

[El_Presidente]

c3 =B3+Row()/10000000

Hello oledere,

Am I understanding correctly that your intention with your formula given here is to add just the tiniest little difference to the values of my height column in order to make them all unique?
That is brilliant! Thank you very much for your help!

[Fluff13]

Another option, without helper cells
=INDEX($A$3:$A$6,AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/($B$3:$B$6=G3),COUNTIF(G$3:G3,G3)))

[oeldere]

@El_Presidente

Yes, that is the idea.

Please also reply on the solution of Fluff13

Glad I could help.

If you are satisfied with the solution, please mark the question solved.

You can add reputation by clicking on the star * add reputation.

[El_Presidente]

Another option, without helper cells
=INDEX($A$3:$A$6,AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/($B$3:$B$6=G3),COUNTIF(G$3:G3,G3)))

Thank you for your answer, Fluff13!
Your formula works perfectly, but I struggle with understanding it completely, to be honest.
Would you mind explaining the logic behind the following parts to me?

=INDEX($A$3:$A$6,AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/($B$3:$B$6=G3),COUNTIF(G$3:G3,G3)))
I get that you are defining the array here, but the method I cannot quite grasp.


=INDEX($A$3:$A$6,AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/($B$3:$B$6=G3),COUNTIF(G$3:G3,G3)))
I get that you are defining the k-smallest value, but still not quite the full story as to how it fits into the logic of the complete formula.

I'd greatly appreciate an explanation since I'm really into learning Excel and not just the "copy-paste-pray" method.

[Fluff13]

This part returns an array of numbers, in this case {1,2,3,4}
(ROW($A$3:$A$6)-ROW($A$3)+1)

then this returns an array of true or false, depending on whether the value in B = G3
($B$3:$B$6=G3)

we then divide the two arrays where true=1 & false =0 which will give an array like {#DIV/0!,2,3,#DIV/0!}

For H3 the countif returns 1, so we get the 1st smallest value & then in H4 the countif will return 2, so we get the 2nd smallest.

The best way to see what is happening is tho use the "Evaluate Formula" option on the formula tab.

[El_Presidente]

Thank you for your explanation, Fluff13!

Today I got the time to really dig into it and undestood how your formula works.

My question has now received two great answers (by oldere, Fluff13) and I am closing this thread as "solved".

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Fluff13]

Thank you for your explanation, Fluff13!

You're welcome & thanks for the feedback.

[Limor_OP]

Another option, without helper cells
=INDEX($A$3:$A$6,AGGREGATE(15,6,(ROW($A$3:$A$6)-ROW($A$3)+1)/($B$3:$B$6=G3),COUNTIF(G$3:G3,G3)))

Hi Fluff13,
Is your formula supposed to be applied in cell H3 and down? For some reason it doesnt work for me.
Also not in array type.
Can you guess why?

[Fluff13]

If your using 2007, then it won't work as the Aggregate function only came into being with 2010.

[Limor_OP]

OK that explains it. Thanks.

[Fluff13]

You're welcome.