Converting Data listed horizontally to vertical listing

[excelnovice_1]

Hello, I have PDFs that I converted to excel. The PDFs contain a Subcontractor, General Contractor, and then every invoice#, invoice date, and invoice amount that that subcontractor has paid. When I convert it to excel the data all gets listed in one row. I would like it to be listed vertically so that for every invoice#/date/amount, there is a new row with the subcontractor and prime listed.

Below is how the data is originally converted.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Subcontractor[/TD]
[TD]General Contractor[/TD]
[TD]Invoice_1[/TD]
[TD]Date_1[/TD]
[TD]Amount_1[/TD]
[TD]Invoice_2[/TD]
[TD]Date_2[/TD]
[TD]Amount_2[/TD]
[TD]Invoice_3[/TD]
[TD]Date_3[/TD]
[TD]Amount_3[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC2[/TD]
[TD]123[/TD]
[TD]10/10/16[/TD]
[TD]100[/TD]
[TD]124[/TD]
[TD]1017/16[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]GC4[/TD]
[TD]ABC[/TD]
[TD]10/11/16[/TD]
[TD]1,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC1[/TD]
[TD]23A[/TD]
[TD]9/6/16[/TD]
[TD]1,500[/TD]
[TD]24A[/TD]
[TD]10/4/16[/TD]
[TD]1,750[/TD]
[TD]25A[/TD]
[TD]10/16/16[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]GC1[/TD]
[TD]INV45[/TD]
[TD]8/3/16[/TD]
[TD]500[/TD]
[TD]INV47[/TD]
[TD]9/1/16[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC3[/TD]
[TD]XYZ[/TD]
[TD]10/21/16[/TD]
[TD]1,000[/TD]
[TD]ZYX[/TD]
[TD]10/27/16[/TD]
[TD]1,200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]GC4[/TD]
[TD]6789[/TD]
[TD]9/19/16[/TD]
[TD]50[/TD]
[TD]6788[/TD]
[TD]9/1/16[/TD]
[TD]2,400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Below is how I would like the data to be presented.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Subcontractor[/TD]
[TD]General Contractor[/TD]
[TD]Invoice #[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC2[/TD]
[TD]123[/TD]
[TD]10/10/16[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC2[/TD]
[TD]124[/TD]
[TD]10/17/16[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]GC4[/TD]
[TD]ABC[/TD]
[TD]10/11/16[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC1[/TD]
[TD]23A[/TD]
[TD]9/6/16[/TD]
[TD]1,500[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC1[/TD]
[TD]24A[/TD]
[TD]10/4/16[/TD]
[TD]1,750[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC1[/TD]
[TD]25A[/TD]
[TD]10/16/16[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]GC1[/TD]
[TD]INV45[/TD]
[TD]8/3/16[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]GC1[/TD]
[TD]INV47[/TD]
[TD]9/1/16[/TD]
[TD]789[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC3[/TD]
[TD]XYZ[/TD]
[TD]10/21/16[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]GC3[/TD]
[TD]ZYX[/TD]
[TD]10/27/16[/TD]
[TD]1,200[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]GC4[/TD]
[TD]6789[/TD]
[TD]9/19/16[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]GC4[/TD]
[TD]6788[/TD]
[TD]9/1/16[/TD]
[TD]2,400[/TD]
[/TR]
</tbody>[/TABLE]

Is there anyway to convert this data in excel, access or ACL analytics?

 

[MickG]

Try this for results on sheet 2.

[COLOR="Navy"]Sub[/COLOR] MG03Nov50
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To 5, 1 To 1)
Ray(1, 1) = "Subcontractor": Ray(2, 1) = "General Contractor"
Ray(3, 1) = "Invoice #": Ray(4, 1) = "Date": Ray(5, 1) = "Amount"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
        [COLOR="Navy"]For[/COLOR] Ac = 3 To Lst [COLOR="Navy"]Step[/COLOR] 3
            c = c + 1
            [COLOR="Navy"]If[/COLOR] c > UBound(Ray, 2) [COLOR="Navy"]Then[/COLOR] ReDim Preserve Ray(1 To 5, 1 To c)
            Ray(1, c) = Dn.Value: Ray(2, c) = Dn.Offset(, 1).Value
            Ray(3, c) = Dn(, Ac): Ray(4, c) = Dn(, Ac + 1): Ray(5, c) = Dn(, Ac + 2)
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet12").Range("A1").Resize(c, 5)
    .Value = Application.Transpose(Ray)
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick