You might have faced this scenario while analyzing data in excel. I'm talking about a case where the data for 1 record is not present in a single line and it uses number of cells in one or other way. One such case is shown below:
Now, if you want data of each block in 1 row or I must say that if you want the data in tabular format then you can write few very simple lines of code and clean the data.
If I assume Sheet1 contains the raw data as shown in 1st image and Sheet2 as the result sheet, then these lines of code are enough to do the job.
Sub xtremeExcel()
For i = 1 To 55
actvrw = Sheet1.Range("A:A").Find(what:=i, searchdirection:=xlNext).Row
lr = Sheet2.Range("A:A").Find(what:="*", searchdirection:=xlPrevious).Row + 1
For x = 1 To 5
Sheet2.Cells(lr, x).Value = Sheet1.Cells(actvrw + (x - 1), 3).Value
Next
For y = 1 To 4
Sheet2.Cells(lr, 5 + y).Value = Sheet1.Cells(actvrw + (y - 1), 6).Value
Next
Next
End Sub
You can watch complete video tutorial here to understand the logic. You can subscribe to our channel to get updates of all video tutorials we upload on youtube.
Extreme Excel Solutions
http://www.ExtremeExcelSolutions.weebly.com
