Custom Sorting (Stumped)

[Et3rnal]

I have 2 colums of data. I want to sort colum B by colum C. Th
problem is my range is about 70 rows, and in that colum there are 3
consecutive rows in the middle that I don't want sorted. I know
could go the long way and do 2 seperate sorts, but I want the sorte
data to be applicable above and below the range I don't want sorted.
Is there a way to do this? Any help would be great.

Thank

 

[Dave Peterson]

Maybe pick up those 30 rows and paste them into a different worksheet.

Do your sort (delete those 30 rows if you need to)

Insert the original rows back into your data.

(might work, but save first. Depending on what you have in your data, it may
not!)

 

[Et3rnal]

Wont work. The range of 30 in the colum I dont want sorted is relativ
to the same row in other colums. If possible I think the only way t
do it is sort aroun

 

[Max]

I have 2 colums of data. I want to sort colum B by colum C. The
problem is my range is about 70 rows, and in that colum there are 30
consecutive rows in the middle that I don't want sorted. I know I
could go the long way and do 2 seperate sorts, but I want the sorted
data to be applicable above and below the range I don't want sorted.
Is there a way to do this? Any help would be great.

Try this little experimental set-up
which might provide some ideas you could use ..

Suppose you have in Sheet1, in B1:C10

Data1 10
Data2 9
Data3 8
Data4 7
Data5 6
Data6 5
Data7 4
Data8 3
Data9 2
Data10 1

where the numbers in col C are assumed unique

And you want to freeze the middle range,
say consecutive rows 4 to 6, i.e. B4:C6
when you sort by col C in ascending order

Let's put a flag, say the number "1" in D46
to identify the range to freeze

In a new Sheet2
--------------------

Put in B1:

=OFFSET(Sheet1!$B$1,MATCH(C1,Sheet1!C:C,0)-1,)

Put in C1:

=IF(Sheet1!D1<>1,OFFSET(Sheet1!$C$1,MATCH(SMALL(Sheet1!C:C,ROW()),Sheet1!C:C
,0)-1,),Sheet1!C1)

Select B1:C1
Copy down to C10 (i.e. as many rows there is data in Sheet1)

In B1:C10 will be the sort desired
with the middle range (i.e. B4:C6 of Sheet1) intact, viz.:

Data10 1
Data9 2
Data8 3
Data4 7 < row intact
Data5 6 < row intact
Data6 5 < row intact
Data4 7
Data3 8
Data2 9
Data1 10

 

[Max]

Apologies, dismiss the suggestion for now ..
there's an error in the output ..

 

[Max]

did you see a white towel fluttering into the ring ?
it's mine <bsg> .. am out of ideas here ..

 

[AlfD]

Well: I've just had a bit of fun here which might be of interest.

I put some random numbers in a column (C4:C20)
I hid rows 10 to 14.
I copied the list : F5: visible items only.
I pasted this in another column.
I sorted it in that new column.
I copied it and pasted it back over the original list (still with th
hidden rows).
I unhid the rows.
It worked.

Al

 

[Max]

Sounds good, Alf !
Thanks ..

 

[Dave Peterson]

It failed for me.

I put =cell("address",a1) in a1 and copied to A1:F20.
I converted to values
I put =d1 in c1 and filled down.

I hid rows 11:15

selected a1:f20, copied just the visible cells and pasted them in a nice spot.
(my formulas were converted to values, too)

Sorted (descending) that 15 row range by the first column and got this:

$A$9 $B$9 $D$9 $D$9 $E$9 $F$9
$A$8 $B$8 $D$8 $D$8 $E$8 $F$8
$A$7 $B$7 $D$7 $D$7 $E$7 $F$7
$A$6 $B$6 $D$6 $D$6 $E$6 $F$6
$A$5 $B$5 $D$5 $D$5 $E$5 $F$5
$A$4 $B$4 $D$4 $D$4 $E$4 $F$4
$A$3 $B$3 $D$3 $D$3 $E$3 $F$3
$A$20 $B$20 $D$20 $D$20 $E$20 $F$20
$A$2 $B$2 $D$2 $D$2 $E$2 $F$2
$A$19 $B$19 $D$19 $D$19 $E$19 $F$19
$A$18 $B$18 $D$18 $D$18 $E$18 $F$18
$A$17 $B$17 $D$17 $D$17 $E$17 $F$17
$A$16 $B$16 $D$16 $D$16 $E$16 $F$16
$A$10 $B$10 $D$10 $D$10 $E$10 $F$10
$A$1 $B$1 $D$1 $D$1 $E$1 $F$1

Copied and pasted over A1 and got this:
row#
$A$9 $B$9 $D$9 $D$9 $E$9 $F$9 1
$A$8 $B$8 $D$8 $D$8 $E$8 $F$8 2
$A$7 $B$7 $D$7 $D$7 $E$7 $F$7 3
$A$6 $B$6 $D$6 $D$6 $E$6 $F$6 4
$A$5 $B$5 $D$5 $D$5 $E$5 $F$5 5
$A$4 $B$4 $D$4 $D$4 $E$4 $F$4 6
$A$3 $B$3 $D$3 $D$3 $E$3 $F$3 7
$A$20 $B$20 $D$20 $D$20 $E$20 $F$20 8
$A$2 $B$2 $D$2 $D$2 $E$2 $F$2 9
$A$19 $B$19 $D$19 $D$19 $E$19 $F$19 10
$A$18 $B$18 $D$18 $D$18 $E$18 $F$18 11
$A$17 $B$17 $D$17 $D$17 $E$17 $F$17 12
$A$16 $B$16 $D$16 $D$16 $E$16 $F$16 13
$A$10 $B$10 $D$10 $D$10 $E$10 $F$10 14
$A$1 $B$1 $D$1 $D$1 $E$1 $F$1 15
$A$16 $B$16 $D$16 $D$16 $E$16 $F$16 16
$A$17 $B$17 $D$17 $D$17 $E$17 $F$17 17
$A$18 $B$18 $D$18 $D$18 $E$18 $F$18 18
$A$19 $B$19 $D$19 $D$19 $E$19 $F$19 19
$A$20 $B$20 $D$20 $D$20 $E$20 $F$20 20

And c1:c15 were now values and c16:c20 remained formulas (rows 16:20 were
untouched).

 

[Dave Peterson]

My next suggestion:

Use a helper column. Put 1, 2, ... 30 in that column for the rows you want to
keep together. Leave the other cells empty.

Then sort your data, but use that helper column as the first key.

Your "don't touch" range should bubble to the top.

And the rest of your data will sink to the bottom in sorted order.

Now just cut those 30 rows at the top and insert them where you want them.

(And then come back to delete those top 30 rows (now empty).)

If you need more than 3 key columns in your sort, make sure you include that
helper column as the primary key each time.

 

[Dave Peterson]

I used xl2002 (but I don't think that it would have mattered).

 

[Max]

Tried a slight variation of what Alf suggested ..

Filled down in C4:C13 with numbers: 10,9,8,...1
Hid a couple of mid range rows 7, 8 and 9
(C7:C9 contained the numbers: 7, 6 and 5)

Selected C4:C13
Did a Data > Sort > Continue with the current selection
Sort by: Column C - Ascending > OK

Unhid rows 7, 8 and 9

And the result was:

1
2
3
7 < intact
6 < intact
5 < intact
4
8
9
10

It does seem that the mid range hidden rows were left intact (unaffected by
the sort) ?
And this might work for what the OP wanted ??

 

[AlfD]

Hi!

FWIW, I'm using Excel 2000.

My experiment carried the hidden columns to the new location but the
were not sorted. However, I did paste on top of the same rows as th
original data was in (some hidden, of course).
Dave pasted them "in a nice spot". Could that be the difference?

Al

 

[Max]

Maybe the "key" operation involved simply
the hiding of the mid range rows prior to sorting of the entire range
in-place,
which preserved these in-between hidden rows exactly
(without using F5 - visible)

As mentioned, in my trial, the mid range hidden rows remained intact
with the rows above and below sorted properly

I did replicate the trial on Dave's more extensive A1:F20
and got it to work as well

 

[AlfD]

Hi!

I thought I'd better test my own theory. Dave's data sorts as require
if pasted into G1. It doesn't when pasted into G21: there's a definit
lack of hidden rows in the sort!

However, it *did* sort in situ: no copying. Maybe this is what i
needed?

Final point: if you hide rows: then select the data area: then copy
then F5 etc the result is very different from doing the F5 routin
*before* the copy. The former handles this problem: the latte
doesn't.

Al