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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
ucastores said:
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
Click to expand...

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(LARGE($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.

---
 
thanks for the reply. works good.

Max said:
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(LARGE($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.

---
Click to expand...
 
Back
Top