Data Seperation in different cell in a column to different rows and columns

[tosinbolaji]

Hi Guys,Please i have a set of data with specific number of columns i need to copy from an app to excel, once i copy and paste, it pastes all in a single column. Can i get help to convert it to columns and rows as its meant to be? (I have 14 columns with topic) Please help me.

The data is like this

16390544

A403716907664

Alat Wema

Port harcourt Rivers Nigeria

CLEMENT OGBONDA

Port Harcourt Rivers Nigeria

Prepaid

0

Pending

0

0

Delivered to Hub

Fedex

15998394

A942153877645

Alat Wema

Port harcourt Rivers Nigeria

GOLDEN OBULOR

Port Harcourt Rivers Nigeria

Prepaid

0

Pending

0

0

Pending

Fedex

<colgroup><col style="width: 137pt;" width="182"> </colgroup><tbody>
[TD="class: xl63, width: 182"]
21-Mar-18
[/TD]

[TD="class: xl63"] 21-Mar-18[/TD]

</tbody>

But i want them like this on excel in differnt rows and columns. Please help out

A942153877645	Alat Wema	Port harcourt Rivers Nigeria	GOLDEN OBULOR	Port Harcourt Rivers Nigeria	Prepaid	Pending	Pending	Fedex
A920701641537	Alat Wema	Port harcourt Rivers Nigeria	JOHN OCHAI	Port Harcourt Rivers Nigeria	Prepaid	Pending	Delivered to Hub	Fedex

<colgroup><col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="mso-width-source:userset;mso-width-alt:2304;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:6619;width:136pt" width="181"> <col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:6656;width:137pt" width="182"> <col style="mso-width-source:userset;mso-width-alt:2011;width:41pt" width="55"> <col style="mso-width-source:userset;mso-width-alt:512;width:11pt" width="14"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:512; width:11pt" span="2" width="14"> <col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:1645;width:34pt" width="45"> </colgroup><tbody>
[TD="class: xl65, width: 69, align: right"]21-Mar-18[/TD]
[TD="width: 63, align: right"]16390544[/TD]
[TD="width: 101"] A403716907664

[/TD]
[TD="width: 75"] Alat Wema
[/TD]
[TD="width: 181"] Port harcourt Rivers Nigeria
[/TD]
[TD="width: 134"] CLEMENT OGBONDA
[/TD]
[TD="width: 182"] Port Harcourt Rivers Nigeria
[/TD]
[TD="width: 55"]Prepaid[/TD]
[TD="width: 14, align: right"]0[/TD]
[TD="width: 58"]Pending[/TD]
[TD="width: 14, align: right"]0[/TD]
[TD="width: 14, align: right"]0[/TD]
[TD="width: 113"] Delivered to Hub
[/TD]
[TD="width: 45"] Fedex
[/TD]

[TD="class: xl65, align: right"] 21-Mar-18[/TD]
[TD="align: right"]15998394[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="class: xl65, align: right"] 21-Mar-18[/TD]
[TD="align: right"]8923280[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

 

[mumps]

Assuming your data is in column A starting at row 1 in Sheet1, this macro will copy the data to Sheet2:

Sub TranposeData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    For x = 1 To LastRow Step 14
        Cells(x, 1).Resize(14).Copy
        Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
    Next x
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub