sort numbers high to low, copy to new cells with associated data.
 

I am trying to make a list of averages sort highest first and move them to
another area of the same worksheet along with the identifiers (Months) in
column to left.
excel 2002.

sort numbers high to low, copy to new cells with associated data.
 

"ucastores" wrote:
I am trying to make a list of averages sort highest first and move them to
another area of the same worksheet along with the identifiers (Months) in
column to left

Assume source cols are E and F,
where col F = averages, col E = months

This non-array construct will draw out the source listing in cols E and F,
into say, cols AB and AC in the required manner, ie with col AC (averages)
sorted in descending order, with the associated col AB (months) adjacent to
it.

In AA2:
=IF(F2="","",F2-ROW()/10^10)
(Leave AA1 empty)

In AB2:
=IF(ROW(A1)COUNT($AA:$AA),"",INDEX(E:E,MATCH(LARG E($AA:$AA,ROW(A1)),$AA:$AA,0)))
Copy AB2 to AC2

Select AA2:AC2, fill down to cover the max expected extent of data in the
source averages col F. Cols AB and AC returns the required results, all
neatly bunched at the top. Lines with tied averages, if any, will be returned
in the same relative order that these appear within the source cols E and F.
Hide away the criteria col AA, if desired. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

sort numbers high to low, copy to new cells with associated da
 

thanks for the reply. works good.

"Max" wrote:

"ucastores" wrote:
I am trying to make a list of averages sort highest first and move them to
another area of the same worksheet along with the identifiers (Months) in
column to left

Assume source cols are E and F,
where col F = averages, col E = months

This non-array construct will draw out the source listing in cols E and F,
into say, cols AB and AC in the required manner, ie with col AC (averages)
sorted in descending order, with the associated col AB (months) adjacent to
it.

In AA2:
=IF(F2="","",F2-ROW()/10^10)
(Leave AA1 empty)

In AB2:
=IF(ROW(A1)COUNT($AA:$AA),"",INDEX(E:E,MATCH(LARG E($AA:$AA,ROW(A1)),$AA:$AA,0)))
Copy AB2 to AC2

Select AA2:AC2, fill down to cover the max expected extent of data in the
source averages col F. Cols AB and AC returns the required results, all
neatly bunched at the top. Lines with tied averages, if any, will be returned
in the same relative order that these appear within the source cols E and F.
Hide away the criteria col AA, if desired. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

sort numbers high to low, copy to new cells with associated da
 

You're welcome! Thanks for feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ucastores" wrote:
thanks for the reply. works good.