Sorting rows and including data below it

[Guest]

Hi, I need to know if what I need to do is possible without going into scripting:

I have a worksheet where the top rows have 2 merged cells, ie. A1 and B1 are merged, so as C1 and D1, E1 and F1, G1 and H1, etc. Under each column, there are data as well, ie. A2=3, A3=4, A4=5, B2="A",B3="B",B4="C",C2="A",C3="1", etc.

What I would like to do is to sort the top rows and once these are sorted, the old data on the columns will follow where their previous header goes, ie. if range A1:B1 is now E1:F1 after sorting, the data on A2 will now become E2, B2 is now F2, etc.

Any ideas on this?

Thanks,
Sinobato

 

[Max]

A little klunky, and there's probably much better solution(s)
out there, but this seems to work ..

Suppose your source table (as described) is in Sheet1,
the merged cells in the top row (Row1), with data rows beneath

In another sheet, say, Sheet2
----------------------------------------

Copy > paste the top row from Sheet1
into the top row (Row1) in Sheet2

Put in A2:

=OFFSET(Sheet1!$A$1,ROW()-1,MATCH($A$1,Sheet1!$1:$1,0)+COLUMN()-2)

Copy A2 across to B2

Put in C2:

=OFFSET(Sheet1!$A$1,ROW()-1,MATCH($C$1,Sheet1!$1:$1,0)+COLUMN()-4)

Copy C2 across to D2

Put in E2:

=OFFSET(Sheet1!$A$1,ROW()-1,MATCH($E$1,Sheet1!$1:$1,0)+COLUMN()-6)

Copy E2 across to F2

Select A2:F2 and copy down
as many rows as there are data rows in Sheet1
--

Now try sorting the top row (Row1) *only* in Sheet2

The rows beneath will follow the sorted top row
(according to their order/placements below the top row in Sheet1)
which gives what you're after ..
--

If desired, for a neater look in Sheet2,
you could suppress zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi, I need to know if what I need to do is possible without going into scripting:

I have a worksheet where the top rows have 2 merged cells, ie. A1 and B1

are merged, so as C1 and D1, E1 and F1, G1 and H1, etc. Under each column,
there are data as well, ie. A2=3, A3=4, A4=5,
B2="A",B3="B",B4="C",C2="A",C3="1", etc.

What I would like to do is to sort the top rows and once these are sorted,

the old data on the columns will follow where their previous header goes,
ie. if range A1:B1 is now E1:F1 after sorting, the data on A2 will now
become E2, B2 is now F2, etc.

 

[Guest]

Max,

This solution did worked! What I'm just afraid is I have a number of columns to work with, actually. But anyway, you gave me the idea, and many thanks for that!

Regards,
Sinobato

--
"Knowledge not shared is useless..."
- Sinobato

A little klunky, and there's probably much better solution(s)
out there, but this seems to work ..

Suppose your source table (as described) is in Sheet1,
the merged cells in the top row (Row1), with data rows beneath

In another sheet, say, Sheet2
----------------------------------------

Copy > paste the top row from Sheet1
into the top row (Row1) in Sheet2

Put in A2:

=OFFSET(Sheet1!$A$1,ROW()-1,MATCH($A$1,Sheet1!$1:$1,0)+COLUMN()-2)

Copy A2 across to B2

Put in C2:

=OFFSET(Sheet1!$A$1,ROW()-1,MATCH($C$1,Sheet1!$1:$1,0)+COLUMN()-4)

Copy C2 across to D2

Put in E2:

=OFFSET(Sheet1!$A$1,ROW()-1,MATCH($E$1,Sheet1!$1:$1,0)+COLUMN()-6)

Copy E2 across to F2

Select A2:F2 and copy down
as many rows as there are data rows in Sheet1
--

Now try sorting the top row (Row1) *only* in Sheet2

The rows beneath will follow the sorted top row
(according to their order/placements below the top row in Sheet1)
which gives what you're after ..
--

If desired, for a neater look in Sheet2,
you could suppress zeros from showing via:
Tools > Options > View tab > Uncheck "Zero values" > OK

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi, I need to know if what I need to do is possible without going into scripting:

I have a worksheet where the top rows have 2 merged cells, ie. A1 and B1

are merged, so as C1 and D1, E1 and F1, G1 and H1, etc. Under each column,
there are data as well, ie. A2=3, A3=4, A4=5,
B2="A",B3="B",B4="C",C2="A",C3="1", etc.

What I would like to do is to sort the top rows and once these are sorted,

the old data on the columns will follow where their previous header goes,
ie. if range A1:B1 is now E1:F1 after sorting, the data on A2 will now
become E2, B2 is now F2, etc.

 

[Max]

Pleasure' Sinobato !
Glad it helped..
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Max,

This solution did worked! What I'm just afraid is I have a number of

columns to work with, actually. But anyway, you gave me the idea, and many
thanks for that!