excel - sort, selected rows??

M

Max

Going by your subject line: Re: 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(Sheet1!$G$1:$G$5,COUNTIF(Sh
eet1!$G$1:$G1,"<>"&"x")),Sheet1!$G$1:$G$5,0)),Sheet1!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(Sheet1!$G$1:$G$5,COUNTIF(Sh
eet1!$G$1:$G1,"<>"&"x")),Sheet1!$G$1:$G$5,0)),Sheet1!A1)

Copy A1 across to C1, fill down to C5 as before

Adapt to suit ..
 
Top