Sorting data with a formula
 

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

Sorting data with a formula
 

On Mon, 21 Sep 2009 13:58:01 -0700, PeteJ
wrote:

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),RO W()),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

Sorting data with a formula
 

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" wrote:

On Mon, 21 Sep 2009 13:58:01 -0700, PeteJ
wrote:

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),RO W()),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

Sorting data with a formula
 

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

Lars-Åke

On Mon, 21 Sep 2009 18:04:01 -0700, PeteJ
wrote:


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" wrote:

On Mon, 21 Sep 2009 13:58:01 -0700, PeteJ
wrote:

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),RO W()),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

Sorting data with a formula
 

Hello Pete,

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

Regards,
Bernd