Sorting data with a formula

[PeteJ]

Probably an obvious answer, but I can't seem to figure it out. I have 2
columns of data, like this:

Apples 7
Oranges 5
Pears 12
Bananas 2

I want to produce a 3rd column that sorts column A based on the value of
Column B, thus the 3rd column would be:

Bananas
Oranges
Apples
Pears

I know how to do that using "Data-Sort", but I want it to be done by
formula so I don't have to manually do the sorting.

Thanks,

Pete

 

[Lars-Åke Aspelin]

Probably an obvious answer, but I can't seem to figure it out. I have 2
columns of data, like this:

Apples 7
Oranges 5
Pears 12
Bananas 2

I want to produce a 3rd column that sorts column A based on the value of
Column B, thus the 3rd column would be:

Bananas
Oranges
Apples
Pears

I know how to do that using "Data-Sort", but I want it to be done by
formula so I don't have to manually do the sorting.

Thanks,

Pete

If the data in column B are all unique, try the following formula in
cell C1:

=INDEX(A$1:A$4,MATCH(SMALL(B$1:B$4,ROW()),B$1:B$4,0))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy down as far as there are data in columns A and B.

If the data in column B are not all unique, but there are no more than
1000 rows of data, all 1 or greater, try the following formula in cell
C1:

=INDEX(A$1:A$4,MATCH(SMALL(ROW()+1000*(B$1:B$4),ROW()),1000*(B$1:B$4)+ROW(),0))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Copy down as far as there are data in columns A and B.

Hope this helps / Lars-Åke

 

[PeteJ]

Thank you Lars-Ake,

I tried both these formulas, and the both work exactly the same, slightly
wrong. Does the data have to start in Row 1? My data is actually starting in
Row 3, and both formulas are producing results that miss the first 2 data
items. Does starting in Row 3 offset the "ROW()" function?

Pete

 

[Lars-Åke Aspelin]

Replace ROW() with ROW()-2 if your data starts in row 3.

Lars-Åke

 

[Bernd P]

Hello Pete,

I suggest to use this example:
http://sulprobil.com/html/sorting.html

Regards,
Bernd