Excel transpose

[Hansraj]

Hi Sir
I have mentioned below table:

ORDER_NO	Reason For Rejection
4532599	Allocation Stock
	Mismatch Packing
	Surplus
	(blank)
4535662	(blank)
4535724	Unavailability of Stock
4535729	Allocation Stock
	Unavailability of Stock
	(blank)
4535738	Allocation Stock
	Mismatch Packing
	(blank)

<tbody>
</tbody>

And I want result as mention below:

ORDER_NO Reason For Rejection Reason For Rejection Reason For Rejection Reason For Rejection 4532599 Allocation Stock Mismatch Packing Surplus (blank) 4535662 (blank) 4535724 Unavailability of Stock 4535729 Allocation Stock Unavailability of Stock (blank) 4535738 Allocation Stock Mismatch Packing (blank) <tbody> </tbody>

<tbody>
</tbody>

Thanks
Hansraj

 

[Special-K99]

Try this

Sub TransTable()
Lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
j = ""
k = 0
For i = 2 To Lastrow
If Worksheets("Sheet1").Cells(i, 1) = "" Then
  l = j: m = m + 1
Else
  l = Worksheets("Sheet1").Cells(i, 1)
  m = 2
  k = k + 1
  Worksheets("Sheet2").Cells(k, 1) = l
End If
j = l
Worksheets("Sheet2").Cells(k, m) = Cells(i, 2)
Next i
End Sub