Sorting data with a formula

P

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
 
L

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top