Currently struggling finding a faster process for sculpting this data:
Several rows with 4 columns with data, i need to create loop code for transposing data from row (range:colum:D-Empty cell) to columns.
Example (Dummy data) attached to this thread, The actual data is 200 rows, giving a product of about 4500 rows when data is transformed.
Thanks in advance for any clue for automating this process, via macros loop or any other suggestion power BI is not an option since i work with Macos.
Create a sheet named "Result" that has the following headers in row 1 starting in column A:
| Car | Country | Brand | Spare Parts |
Try this macro:
Sub Transposedata()
Application.ScreenUpdating = False
Dim car As Range, LastRow As Long, lCol As Long, srcWS As Worksheet, desWS As Worksheet, x As Long: x = 1
Set srcWS = Sheets("Initial data")
Set desWS = Sheets("Result")
With srcWS
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each car In .Range("A2:A" & LastRow)
lCol = .Cells(car.Row, .Columns.Count).End(xlToLeft).Column
With desWS
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(lCol - 3, 3) = Array(car, car.Offset(, 1), car.Offset(, 2))
.Cells(.Rows.Count, "D").End(xlUp).Offset(1).Resize(lCol - 3) = Application.Transpose(car.Offset(, 3).Resize(, lCol - 3))
End With
Next car
End With
Application.ScreenUpdating = True
End Sub
End SubDon’t have an account yet? Register yourself now and be a part of our community!