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

Bria

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:

Ernesternesta

+1. However, it seems the code missed the time format from cells on column B in the "before" phase, at columns B, F and J in the "after" phase. – Fingering

Answer 2

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.

Fingering

Answer 3

As per this, there are several options:

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

2. You could also use INDEX.

Foretopsail