move row data to columns

  • Thread starter Thread starter Paula
  • Start date Start date
P

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)
 
Paula said:
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
 
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
 
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
 
Back
Top