Jtdylktuy

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?

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.

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

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)))}

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