@onir0110
If you are open to a VBA solution, please place the following code on a Standard Module like Module1.
In the attached, click on the Transpose Data button to get the desired output.
Sub TransposeData()
Dim lr As Long
Dim rngArea As Range
Dim Rng As Range
Dim destCol As Long
Dim r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
destCol = 5
On Error Resume Next
Set rngArea = Range("A2:A" & lr).SpecialCells(xlCellTypeConstants, 3)
On Error GoTo 0
Cells(1, destCol).CurrentRegion.Offset(1).Clear
r = 2
If Not rngArea Is Nothing Then
For Each Rng In rngArea.Areas
Rng.Copy
Cells(r, destCol).PasteSpecial xlPasteAll, Transpose:=True
r = r + 1
Application.CutCopyMode = 0
Next Rng
End If
Cells(2, destCol).Select
Application.ScreenUpdating = True
End Sub
