Converting Vertical Columns in to Horizontal Rows - (Better solution to Transpose)

This can be easily done with a small macro. Would you be interested?

Try this code

Option Explicit

Sub transpose_data()
Dim i As Long, lrow As Long, lcol As Long, lcol1 As Long

Application.ScreenUpdating = False

With Worksheets("current data")
    lrow = .Range("B" & .Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If .Range("A" & i).Value = "" Then
            lcol = .Range("IV" & i - 1).End(xlToLeft).Column
            lcol1 = .Range("IV" & i).End(xlToLeft).Column
            .Range(.Cells(i, 2), .Cells(i, lcol1)).Copy .Cells(i - 1, lcol + 1)
            Application.CutCopyMode = False
            .Rows(i).Delete
        End If
    Next i
End With

MsgBox "Done"
Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Ok, so the output is fine right? It just needs to show up in a different sheet?

Try this

Option Explicit

Sub transpose_data()
Dim i As Long, lrow As Long, lcol As Long, lcol1 As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = Worksheets("want result by Formula")
ws.Cells.ClearContents

Worksheets("current data").Cells.Copy ws.Range("A1")

With ws
    lrow = .Range("B" & .Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If .Range("A" & i).Value = "" Then
            lcol = .Range("IV" & i - 1).End(xlToLeft).Column
            lcol1 = .Range("IV" & i).End(xlToLeft).Column
            .Range(.Cells(i, 2), .Cells(i, lcol1)).Copy .Cells(i - 1, lcol + 1)
            Application.CutCopyMode = False
            .Rows(i).Delete
        End If
    Next i
End With

MsgBox "Done"
Application.ScreenUpdating = True

End Sub

Since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.