How to arrange data in one column in excel?

Question

Database

There is a table:
id1 a b
id2 c de
id3 fg
It is necessary to bring it to the form
id1 a
id1 b
id2 c
id2 d
and so on.
rows 150 thousand columns 20
How? Are there methods? Or ideas?

Answer 1

Alt + F11, write a macro there that will look at the columns on the right for the presence of values ​​\u200b\u200band, if found, add a line, enter this value.
In principle, nothing complicated. There seems to be no other way here (in the sense, without VBA)

Sub Обработка()
    Dim row As Long, column As Long, i As Long
    row = 1 ' строка, с которой начинаем обработку
    column = 1 ' столбец с id
    
    Do While Len(Cells(row, column).Value) <> 0
    
        i = 2
        Do While Len(Cells(row, column + i).Value) <> 0
        
            Rows(row + i - 1 & ":" & row + i - 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(row + i - 1, column).Value = Cells(row, column).Value
            Cells(row + i - 1, column + 1).Value = Cells(row, column + i).Value
            Cells(row, column + i).Value = ""
            i = i + 1
            
        Loop
        
        row = row + 1
    Loop
    
End Sub

At 150 thousand, it can stretch for a couple of minutes, but, most importantly, it will fulfill its task.
If you will often use the solution: you can optimize by reading it all into memory for calculations