Advanced sorting

[Gerben Dirksen]

Hi, I'm sure someone has asked this before, but I can't find it.

I'm looking for a function that sorts a set of data that contains
references. Example, I have this data:

Apple $1
Banana $5
Citrus $3

Now I want to automatically create a list in a different place that
contains the same data, but sorted to value, in this case the result
would be:

Banana $5
Citrus $3
Apple $1

The original table should stay as it is and be changable, i.e if I
change the value of an apple to $4, the new table should automatically
be updated to:

Banana $5
Apple $4
Citrus $3

I tried simple referencing and then sorting, but then it will simply
copy the order from the original table.

Thanks for any help!

Gerben

 

[RagDyeR]

Are the items and the values in different columns?

Might there be duplicate values?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Hi, I'm sure someone has asked this before, but I can't find it.

I'm looking for a function that sorts a set of data that contains
references. Example, I have this data:

Apple $1
Banana $5
Citrus $3

Now I want to automatically create a list in a different place that
contains the same data, but sorted to value, in this case the result
would be:

Banana $5
Citrus $3
Apple $1

The original table should stay as it is and be changable, i.e if I
change the value of an apple to $4, the new table should automatically
be updated to:

Banana $5
Apple $4
Citrus $3

I tried simple referencing and then sorting, but then it will simply
copy the order from the original table.

Thanks for any help!

Gerben

 

[Gerben Dirksen]

Hi again,

the column to sort on are all unique; the items and values are in
different columns.

Gerben

 

[Lars-Åke Aspelin]

Hi, I'm sure someone has asked this before, but I can't find it.

I'm looking for a function that sorts a set of data that contains
references. Example, I have this data:

Apple $1
Banana $5
Citrus $3

Now I want to automatically create a list in a different place that
contains the same data, but sorted to value, in this case the result
would be:

Banana $5
Citrus $3
Apple $1

The original table should stay as it is and be changable, i.e if I
change the value of an apple to $4, the new table should automatically
be updated to:

Banana $5
Apple $4
Citrus $3

I tried simple referencing and then sorting, but then it will simply
copy the order from the original table.

Thanks for any help!

Gerben

Try this formula on row 1 in any column:

=INDEX(A$1:A$100,MATCH(LARGE($B$1:$B$100,ROW()),$B$1:$B$100,0))

Copy the formula to the next column to the right and then copy down as
far as you have rows with data in your first table.

This formula does only work if there are never more than one fruit
with the same price. If that could happen, some other formula will
have to be used,


Hope this helps / Lars-Åke

 

[Ragdyer]

My question was ... might there be duplicate *values*?

Peaches - 1
Apples - 3
Pears - 3
Bananas - 2
Grapes - 2
Oranges - 8

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Hi again,

the column to sort on are all unique; the items and values are in
different columns.

Gerben