I think that a formula would be quite complicated, while macro works quick and is easy to read:
Sub test()
Dim i As Long, j As Long, k As Long, l As Long
k = 2
l = Cells(Rows.Count, "H").End(xlUp).Row
Range("H2:I" & IIf(l < 2, 2, l)).ClearContents
For i = 1 To 6
l = Cells(Rows.Count, i).End(xlUp).Row
If l > 1 Then
For j = 2 To l
Cells(k, "H") = Cells(1, i)
Cells(k, "I") = Cells(j, i)
k = k + 1
Next j
End If
Next i
End Sub
if you want not only valuees but also formatting then instead of
Cells(k, "H") = Cells(1, i)
Cells(k, "I") = Cells(j, i)
use
Cells(1, i).copy Cells(k, "H")
Cells(j, i).copy Cells(k, "I")
Bookmarks