Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel - sort, selected rows??
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel - sort, selected rows??
Going by your subject line: excel - sort, selected rows??
here's one non-array formulas play to try .. Assume this table is in A1:C5 in Sheet1 9 T1 R1 3 T2 R2 5 T3 R3 7 T4 R4 6 T5 R5 and we want to sort the table in ascending order by the numbers in col A, but only for rows 1,3,5 (rows 2 & 4 are to be left untouched) Using an empty col to the right, say, col G, Put in G1: =IF(ISNUMBER(MATCH(ROW(A1),{1;3;5},0)),A1+ROW()/10^10,"x") Copy G1 down to G5 In a new Sheet2, Put in say, A1: =IF(Sheet1!$G1<"x",INDEX(Sheet1!A:A,MATCH(SMALL(S heet1!$G$1:$G$5,COUNTIF(Sh eet1!$G$1:$G1,"<"&"x")),Sheet1!$G$1:$G$5,0)),Shee t1!A1) Copy A1 across to C1, fill down to C5 A1:C5 will return the desired sort: 5 T3 R3 3 T2 R2 6 T5 R5 7 T4 R4 9 T1 R1 -- And to similarly sort in *descending* order by the numbers in Sheet1's col A, and again only for rows 1,3,5, we could use these 2 slightly revised formulas: Put instead in G1: =IF(ISNUMBER(MATCH(ROW(A1),{1;3;5},0)),A1-ROW()/10^10,"x") And in Sheet2, put instead in A1: =IF(Sheet1!$G1<"x",INDEX(Sheet1!A:A,MATCH(LARGE(S heet1!$G$1:$G$5,COUNTIF(Sh eet1!$G$1:$G1,"<"&"x")),Sheet1!$G$1:$G$5,0)),Shee t1!A1) Copy A1 across to C1, fill down to C5 as before Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- " wrote in message ... current: |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel - sort, selected rows??
After the lines
Put instead in G1: =IF(ISNUMBER(MATCH(ROW(A1),{1;3;5},0)),A1-ROW()/10^10,"x") Copy G1 down to G5, as before -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort order : Excel vs imported data | Excel Discussion (Misc queries) | |||
Select rows and sort based on type | Excel Discussion (Misc queries) | |||
How do I sort for the maximum values in each year in excel? | Excel Discussion (Misc queries) | |||
SORTING question | New Users to Excel | |||
Can Excel sort rows by color? | Excel Discussion (Misc queries) |