This formatting issue is confusing me. I'm not an Excel king and would greatly appreciate the solution to my problem.
I'm trying to format data from multiple columns into a single row by date. i.e.:
I've tried to search for solutions regarding transpose, but this seems a bit more involved. I have 4x data results for each date (as seen in the before column).
Here is a simple loop that works bottom up in the sheet, shifts last line over 4 columns, copies line above down and then deletes the line above.
Sub TransposeData()
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long
Set WS = Worksheets("Sheet1")
With WS
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
LastCellRowNumber = LastCell.Row
End With
'Loop through column A bottom up
For i = LastCellRowNumber To 2 Step -1
'Shift current values over
Range("A" & i & ":D" & i).Insert Shift:=xlToRight
'Copy new values down
Range("A" & i & ":D" & i).Value = Range("A" & i - 1 & ":D" & i - 1).Value
'Delete row
Rows(i - 1).Delete Shift:=xlUp
Next i
End Sub
Before:
After:
You can use NotePad++ to unite the rows as from a .csv file, and then, import the new formatted information on Excel again.
1- Save your spreadsheet in comma separated value format (.csv).
2- In NotePad++, click on: Edit->Line Operations->Join Lines.
3- Replace spaces (" ") by commas(","). You should get 3 replacements.
Now you have just one line with all values separated by commas.
4- Save and import this new file on Excel.
As per this, there are several options:
Item 3: enter in A8
=OFFSET($A$2,INT((COLUMN()-COLUMN($A$8))/4),MOD(COLUMN()-COLUMN($A$8),4))
Copy to the right as needed.
You could also use INDEX.
