Rearrange Data

  • Thread starter Thread starter Maggie
  • Start date Start date
M

Maggie

I have a spreadsheet with over 200 sets of data. Each set is 4 rows
identied by the letters a b c d in one column and the corresponding
data in the next column.

I would like to change this so that the headings are repeated only once
at the top of the sheet and the corresponding data listed below

e.g.

From

A - 123
B - 456
C - 789
D - 147

A - 258
B - 369
C - 987
D - 654

TO

A B C D
123 456 789 147
258 369 987 654


Can Anyone help with a simple way to rearrange htis?
 
Maggie

So long as there is no gap between the sets of ABCD, then the code below
will insert a new sheet called New Data and place the headings ABCD with the
data beneath it. I fear you may have gaps in the data. If you do, the code
could be altered.

If you are not sure how to copy the code over or need it altering, post back

Sub moveData()
Dim lRow As Long
Dim lNewRow As Long
Dim lLastRow As Long
Dim shtNew As Worksheet
Dim shtOrig As Worksheet

Set shtOrig = ActiveSheet
Set shtNew = Worksheets.Add
shtNew.Name = "New Data"
shtNew.Range("A1:D1").Value = Array("A", "B", "C", "D")
lRow = 1
lNewRow = 2
lLastRow = shtOrig.Range("A65536").End(xlUp).Row
For lRow = 1 To lLastRow
Range("A1:D1").Value = Array("A", "B", "C", "D")
shtOrig.Range("B" & lRow & ":B" & lRow + 3).Copy
shtNew.Range("A" & lNewRow).PasteSpecial Transpose:=True
lRow = lRow + 3
lNewRow = lNewRow + 1
Next
End Sub
 
Assuming that the ilustrated data is in A1:C10, and that the functions
in the freely downloadable file at http://home.pacbell.net/beban are
available to your workbook:

Enter A,B,C,D in D1:G1; then in D2:H3 array enter

=ArrayReshape(B1:B10,2,5)

If desired, then clear contents of column H

Alan Beban
 
Back
Top