Excel - Sort by formula when there are duplicate values

Question

My data will change, therefore I am using dynamic lists. Sorting needs to be dynamic as well. I do not want to click on the built in sort function.

I'm sorting dynamic data by formula, but it fails when the sort data is the same. For example:

A B Marham 240 Sigonella 400 Toulon 400 Ghedi 150 Lajes 2000 Nordholz 400

Sorting by column B and returning column A values, the result should be:

Lajes 2000 Sigonella 400 Toulon 400 Nordholz 400 Marham 240 Ghedi 150

Note: I don't care about the order of the duplicated values among themselves (the "400"s).

With this formula:
{=INDEX($A$1:$A$6, MATCH(SMALL(COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), ROW(1:1)), COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), 0))}

The results are:

Lajes 2000 Sigonella 400 Sigonella 400 Sigonella 400 Marham 240 Ghedi 150

The MATCH is tripping me up. How do I get around this?

Answer 1

You can get around duplicates by adding a hidden column C, where you add to your values some very little numbers, small enough not to be obtainable by other means and not to interfere with the original values, which let you differentiate between duplicates, e.g.

  A          B          C
Marham      240      240,0001
Sigonella   400      400,0002
Toulon      400      400,0003
Ghedi       150      150,0004
Lajes      2000     2000,0005
Nordholz    400      400,0006

Then you just sort by the values in column C

Alternatively, if you don't want any hidden columns, you can put the values in like this:

  A            B
Marham      240,0001
Sigonella   400,0002
Toulon      400,0003
Ghedi       150,0004
Lajes      2000,0005
Nordholz    400,0006

but format the fields to display numeric values with 0 digit precision, so excel keeps your detailed values but displays them as:

  A          B
Marham      240
Sigonella   400
Toulon      400
Ghedi       150
Lajes      2000
Nordholz    400

Answer 2

Here's what I came up with... It's not exactly what I was looking for...but it worked.

Using the same data above, I changed the INDEX to return the score instead of the bases:

{=INDEX($B$1:$B$6, MATCH(SMALL(COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), ROW(1:1)), COUNTIF($B$1:$B$6, "<"&$B$1:$B$6), 0))}

Then to return the bases, I used this formula which returns multiple matching values:

{=index(A$1:A$6,small(if(b$1:b$6=b1,row(b$1:b$6)),countif(b$1:b$6,b1)))}

I'd still like to get the original formula working on its own, if anyone has a suggestion.

Answer 3

I know this was forever ago, but the formulas in the other answers didn't work for me. However, I did get it to work with a few minor modifications:

{=INDEX($A$1:$A$7,SMALL(IF(B$1:B$6=B1,ROW(B$1:B$6)),COUNTIF(B1:B$6,B1)))}

Answer 4

How about =sort(a2:b7,2,true) in cell C2?