Get the table into Excel. Let the data table start with the blank cell above the A value in cell A1. See the screenshot of the input sheet
Try this macro:
Option Explicit Sub convert() Dim wssrc As Worksheet Dim wstarget As Worksheet Dim i As Long Dim lrow As Long Set wssrc = ThisWorkbook.Worksheets("Sheet1") Set wstarget = ThisWorkbook.Worksheets("Sheet2") wssrc.Activate lrow = Cells(Rows.Count, "A").End(xlUp).Row Debug.Print lrow For i = 1 To lrow wssrc.Range(Cells(i + 1, 1), Cells(i + 1, i)).Copy wstarget.Range("A" & i) Next i End Sub
This is a screenshot of the input sheet:
This is a screenshot of the output sheet.