Sleeper: Moving data from rows to adjacent columns

[sanjayrbajaj]

Hi Experts

A client of ours has a variable data job. He has data in mysql data base and can give us the data in an excel list too. The issue is that i need records 2 - 5 to be moved into the columns adjacent to the data of record 1 in row 1. The data in records 6 - 10 should be moved into adjacent columns of record 2 in row 2 and so on.

I am attaching herewith a sample of the format required. In sheet1 we have the original data. I have highlighted 5 rows in different colors. In sheet 2 i have shown how the data should be rearranged. I would like to know if there is a macro or script action which can do this accurately for us. There are over 1 lac records and we do not have the time to do this manually. Moreover there is the possibility of errors in doing this manually

Regards

Sanjay R Bajaj

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim cell As Range
Dim sh As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 2 To LastRow Step 5
For j = 1 To 4
.Cells(i + j, "B").Resize(, LastCol - 1).Cut .Cells(i, (LastCol - 1) * j + 2)
Next j
Next i
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub