Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
How I sort data in Excel, it keeps saying "merged cells must be ID | Excel Worksheet Functions | |||
Copy sheet 1 data to sheet 2 cells. | Excel Worksheet Functions | |||
Can it be done | Excel Worksheet Functions | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |