So I have an excel file filled with 10,000 rows of data that is only on one column. The problem is it's not in the correct format. The columns have a pattern of:
"name" "address" "city" "state" "name" "address" "city" "state" "name" ...
but the problem is I need it to be "name" "address" "city" "state". I was wondering if there is a way to do this in Excel or maybe using a script. Any help would be appreciated. Thank you.
If your getting the data from sql... Change your query from vertical to horizontal using pivot. Now you don't have to worry about converting it in excel. See this post
@Laurence has a good formula based solution, however,if you need vba because your doing this over and over with new data dumps from sql here is a VBA solution.
Started with this:
Used this Macro:
Sub TransposeColA()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
cntr = 1
For rowNum = 1 To LastRow
If Cells(rowNum, 1).Value <> "" Then
Set rngA = Range(Cells(rowNum, 1), Cells(rowNum, 1).Offset(3, 0).Address)
Range("B" & rowNum & ":E" & rowNum).Value = Application.Transpose(rngA)
rngA.Delete
Else
Rows(rowNum).Delete Shift:=xlUp
rowNum = rowNum - 1
cntr = cntr + 1
If cntr = LastRow Then Exit Sub
End If
Next rowNum
End Sub
And ended up with this:
Here's a way to manually cross tab data in Excel:
Assume the data is in column A. Enter the following forumlae:
B1: =INT((ROW() - 1 ) * 4)
C1: =INDIRECT("A" & ($B1 + COLUMN() - COLUMN($B1)))
Now select C1 and drag the bottom right corner handle right four cells. You should see the first row of your data displayed
Finally select B1 to F1. Drag the bottom right corner handle down. The further down, the more rows you'll get.
