excel - Move data from multiple columns into single row

Question

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).

Answer 1

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: