move row data to columns

[Paula]

We had a problem with some test data and instead of test results being
recorded in multiple rows 5 columns wide, all of the data was recorded
in one row multiple columns wide. The data consists of 5 readings
taken every second for a period of 2 hours. Each reading is in it's
own cell (no cell contains more than one reading). Do you know of a
way that I can move each set of readings (5 test results) into it's
own 5 column wide row?

i.e. currently shows in single row as:
0, 0, 0, 0, 0, 128, 128, 128, 128, 128, 234, 234, 234, 234, 234, etc.

and I need in 5 column wide rows:
0, 0, 0, 0, 0
128, 128, 128, 128, 128
234, 234, 234, 234, 234
(comma's used in place of actual cells)

 

[Frank Kabel]

We had a problem with some test data and instead of test results being
recorded in multiple rows 5 columns wide, all of the data was recorded
in one row multiple columns wide. The data consists of 5 readings
taken every second for a period of 2 hours. Each reading is in it's
own cell (no cell contains more than one reading). Do you know of a
way that I can move each set of readings (5 test results) into it's
own 5 column wide row?

i.e. currently shows in single row as:
0, 0, 0, 0, 0, 128, 128, 128, 128, 128, 234, 234, 234, 234, 234, etc.

and I need in 5 column wide rows:
0, 0, 0, 0, 0
128, 128, 128, 128, 128
234, 234, 234, 234, 234
(comma's used in place of actual cells)

Hi Paula
try the following on a new sheet. Enter the following in cell A1:
=OFFSET('Sheet 1'!$A$1,0,COLIMN()-1+(ROW()-1)*5)
and copy down and to the left

After this select and copy these new range and insert the values with
'Paste Special' as values

Frank

 

[Max]

Assuming your data is in Sheet1, in A1, A2, A3 etc across

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

Put in A1: =OFFSET(Sheet1!$A$1,0,COLUMN()+ROW()*5-6)

Copy A1 across to E1, then copy down until zeros appear

Cols A to E will return the data in the format desired

 

[Max]

=OFFSET('Sheet 1'!$A$1,0,COLIMN()-1+(ROW()-1)*5)

 

[Frank Kabel]

Hi Max
thanks for pointing that out
Frank

 

[Max]

Delighted to help, Frank !

Rgds
Max
xl 97

 

[Gord Dibben]

Paula

If you're doing this quite often, you may want to use a macro.

Assumes data starts on row one.

Sub WrapUnder()
''wrap 15 columns and multi rows to 5,5,5 in groups
Dim I As Long
For I = Range("A1").End(xlDown).Row To Range("A1").Row Step -1
Cells(I + 1, 1).EntireRow.Insert
Cells(I + 1, 1).EntireRow.Insert
Cells(I, 1).Range("f1:J1").Cut Cells(I + 1, 1)
Cells(I, 1).Range("K1:O1").Cut Cells(I + 2, 1)
Next I
End Sub

Gord Dibben Excel MVP