We can use Excel Macros to organize our data. Below is one such example. Here what we going to do is split long row of data into multiple rows. Here the rule is we need 5 cells per row, then to start the next row. So data in F1 will move to A2, G1 to B2 and so on. Same on third row until data runs out.
Here is how our data currently in the sheet.
Below is the end result we need.
So here is the sample code to do that. You can customize the code to suit to your situation. New sheet will be added and result will be created in that sheet. At last sheet will renamed as "Result sheet" and activated. And you will get a confirmation message at end.
|
Dim WS As Worksheet Dim WS_Result As Worksheet Set WS = Worksheets("Input file") Set WS_Result = Worksheets.Add 'find last column Lastcol = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Counter = 1 ResultRowCount = 1 For i = 1 To Lastcol If Counter = 6 Then Counter = 1 ResultRowCount = ResultRowCount + 1 Else WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i) End If WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i) Counter = Counter + 1 Next i WS_Result.Name = "Result sheet" WS_Result.Activate MsgBox "Completed!", vbInformation, "" |
