Cut and Paste Hell

Firstly, I am new to this forum and so apologise if this post is in the wrong section. Here's my problem...

I have a group of data (lets call it Group 1). It exists in six columns and six rows. I need to take the 2nd row and place it alongside the first row - in six new columns. So now I have 12 columns all on the same row. I take third row and place (move it with cut 'n paste) it in the 1st row (next to the second row of data I've just pasted). So now I have 18 columns. I do the same with the 4th, 5th and 6th rows.

So - what did exist in a 6x6 format now exits in a 1x36 formats. One row and 36 columns.

And that's for Group 1 data. I have 152 groups like that.

Everything in the 1st row of each group has to be under each other.
So, that's 152 rows of the first lines of data. Taking up 6 columns.
Everything in the 2nd row has to be under each other.
So that's 152 rows for the second lines of data. Taking up the NEXT 6 columns.

And so on. And so on for each row of each Group. It isn't a sort data or a transpose problem.

I've done over 1000 cut and pastes. I've tried a macro but can't get it to work. When I record it for Group 1, all is ok (it's recording and so it would be). When I run it (for Group 2), then Group 1 data is overwritten.

It's driving me mad and has taken me hours.

Help appreciated. Thank you.

Hi,

Would this help you. Enter this in A7 and drag down then across

=IF(MOD(ROW(A1),7)=0,OFFSET(A$1,6,0),OFFSET(A$1,MOD(ROW(A1)-1,6),0))

VBA Noob

This macro will turn any 6x6 array into a 1x36 array. It assumes that the array is surrounded by blank cells

Sub Flatten6x6Array()
Dim myRange As Range
Set myRange = Selection.CurrentRegion.Range("a1")
myRange.Offset(0, 6).Range("a1:f1").Value = myRange.Range("a2:f2").Value
myRange.Offset(0, 12).Range("a1:f1").Value = myRange.Range("a3:f3").Value
myRange.Offset(0, 18).Range("a1:f1").Value = myRange.Range("a4:f4").Value
myRange.Offset(0, 24).Range("a1:f1").Value = myRange.Range("a5:f5").Value
myRange.Offset(0, 30).Range("a1:f1").Value = myRange.Range("a6:f6").Value
myRange.CurrentRegion.Offset(1, 0).Clear
End Sub

Thanks for the input. I tried both methods.

Mallycat - didn't have a clue what you were talking about (I'm new remember!). So, experimented and BINGO, it all works 100% as I wanted it to. So I learnt something as well as getting a resolve.

Right now, I have a big smile on my face.

Thank you so much.

Thread closed.