Help with VBA/macro for Excel data

[MB]

Thanks in advance.

I have the following data that I need to manipulate. I am proficient
with Excel formulas (or so I thought), but I can not think of one to
use. Maybe a VBA/macro will do the trick. I am not familiar with much
if any VBA.

I have 20,000+ rows of data. The monthly data came in double stacked
(in 2 rows). Feb - Jul over top of Aug - Jan. I would like the data
Feb - Jan in a single row. In the first column the numbers repeat 1,1,
then 2,2, then 3,3... The columns represent the months. I need to cut
the data out of the 2nd occurance of the row number and paste it at
the end of the 1st occurance. Then delete the row. Then move to the
next number and repeat. Email with questions.

A sample of the data:
FEB MAR APR MAY JUN JUL
AUG SEP OCT NOV DEC JAN

1 -63 62 96 93 -64 -29
1 66 -21 -63 -78 -84 -24
2 -12 80 10 66 31 14
2 22 -22 36 40 -69 11
3 -56 15 80 -30 38 -38
3 28 84 -17 16 -12 14

 

[Frank Kabel]

Hi
if your data starts in row 3 (column B9 on the first sheet try the
following formula in cell A2 on a second sheet
=OFFSET('sheet1'!$B$3,(ROW()-3)*2+INT((COLUMN()-1)/6),MOD(COLUMN()-1,6)
)
and copy 12 cells to the right and down as far as needed

Afterwards copy these values and insert them again with 'Edit - Paste
Special - Values' to remove the formulas

 

[Don Lloyd]

Hi,

For simplicity, the following is based on the basic data starting in A1 i,e,
cells(1, 1)
It works by copying the second data block to the cell next to the first
block (Column 7), deleting the row containing the second data block and
repeating for the next block until a row is encountered where the first
column contains a blank.

Sub DataLayout()
Dim Rw, Col
Rw = 2
Do
Range(Cells(Rw, 1), Cells(Rw, 6)).Copy Cells(Rw - 1, 7)
Rows(Rw).EntireRow.Delete
Rw = Rw + 1
Loop Until IsEmpty(Cells(Rw, 1))
End Sub

Note that the procedure ends when a blank cell occurs in Column 1.

Hope that starts you off.

Regards,
Don