Advanced sorting

  • Thread starter Thread starter Gerben Dirksen
  • Start date Start date
G

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
 
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
 
Hi again,

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

Gerben
 
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
Click to expand...


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
 
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
 
Back
Top