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.

Subcontractor	General Contractor	Invoice_1	Date_1	Amount_1	Invoice_2	Date_2	Amount_2	Invoice_3	Date_3	Amount_3
S1	GC2	123	10/10/16	100	124	1017/16	500
S2	GC4	ABC	10/11/16	1,000
S1	GC1	23A	9/6/16	1,500	24A	10/4/16	1,750	25A	10/16/16	2,000
S3	GC1	INV45	8/3/16	500	INV47	9/1/16	789
S1	GC3	XYZ	10/21/16	1,000	ZYX	10/27/16	1,200
S3	GC4	6789	9/19/16	50	6788	9/1/16	2,400

<tbody>
</tbody>

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

Subcontractor	General Contractor	Invoice #	Date	Amount
S1	GC2	123	10/10/16	100
S1	GC2	124	10/17/16	500
S2	GC4	ABC	10/11/16	1,000
S1	GC1	23A	9/6/16	1,500
S1	GC1	24A	10/4/16	1,750
S1	GC1	25A	10/16/16	2,000
S3	GC1	INV45	8/3/16	500
S3	GC1	INV47	9/1/16	789
S1	GC3	XYZ	10/21/16	1,000
S1	GC3	ZYX	10/27/16	1,200
S3	GC4	6789	9/19/16	50
S3	GC4	6788	9/1/16	2,400

<tbody>
</tbody>

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