Move repeating columns into rows

The prior answers show how to restructure the data using Power Query but to your question of keeping the comments that are in the cells when reformatting the structure I'm pretty sure you would have to use a custom macro for that.  I created one here and will try to attach a sample file next:

Sub reformat_table()
    Application.ScreenUpdating = False
    Dim in_table, out_cell As Range
    Dim repeating_starting_column, repeating_count As Integer
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Set in_table = ws.Range(InputBox("Select table range with repeating columns", "Input Table Range", Selection.Address))
    Set out_cell = ws.Range(InputBox("Select cell for where output should be placed", "Output Table Location"))
    repeating_starting_column = 0 + InputBox("Column number where repeat begins", "Repeat Start")
    repeating_count = 0 + InputBox("Number of Columns that repeat", "Repeat Count")
    in_table.Range(in_table.Cells(0, 0), in_table(0, repeating_starting_column + repeating_count - 2)).Copy (out_cell.Cells(1, 1))
    k = 2
    For i = 1 To in_table.Rows().Count - 1
        For j = repeating_starting_column - 1 To in_table.Columns().Count - 1 Step repeating_count
            If (in_table.Cells(i + 1, j + 1).Value2 <> "") Then
                in_table.Range(in_table.Cells(i, 0), in_table(i, repeating_starting_column - 2)).Copy (out_cell.Cells(k, 1))
                in_table.Range(in_table.Cells(i, j), in_table(i, j + repeating_count - 1)).Copy (out_cell.Cells(k, 0 + repeating_starting_column))
                k = k + 1
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

If you highlight the source table before running the macro it will default that address range,  then it will ask for the destination (cell), then the column where the first of the repeated columns starts (e.g. 2 in this example), and finally the number of columns that get repeated (e.g. again 2 in this example).

John5​,

See the screenshot above. The name of data in range A1:G4 is Table. Use below M code in Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {
    [Hours = [Hours], Date = [Date]],
    [Hours = [Hours2], Date = [Date3]],
    [Hours = [Hours4], Date = [Date5]]
}),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Hours", "Date"}, {"Hours", "Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom1", each [Hours] <> null),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Hours", type number}, {"Name", type text}})
in
    #"Changed Type"

Hope this helps.

IlirU