VBA Use dynamic array to create custom report

[DFlem]

Please assist if you can! I have an exported Excel report, each row is a new customer, but the rows stop at 137. There are about 250 columns with customer details, but only 5 columns belong to 1 customer in each row. My boss wants me to format this report to reflect each customer in individual rows. I have attempted stacking, transposing, unpivoting, dynamic arrays and index. However, when creating my 2 dimensional dynamic array for the dynamic report, my output is listing everything except my customer columns into rows. Is there a way for me to custom output my rows and columns into specific rows or is there an easier way.

Also another catch is that the first few columns in my worksheet includes data that must be applied to everyone individual customer in that corresponding row. I have attached an image for additional reference.

Any assistance is greatly appreciated!

 

[igold]

I am sorry I asked!

Can you apply the headers to my correct required output in Post #7

 

[igold]

I am having trouble reconciling my response in Post #7- As I re-visit it, it would seem that this should be the correct result... Disregarding the Header Row.

Split text.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Work Type	M W 0#	M P O#	C WO#	CPO3	P NUM	TICKET NUM	CUSTOMER COL 2	READY/PERMIT 2	TYPE 2	SM 2	QUANTITY 1
2	XXXX	123	1111	0	123	234567	344547764	CAST	READY	TRANSFER	122348	1
3	XXXX	123	1111	0	123	234567	344547764	JIM	PERMIT	PULL	35674	1
4	XXXX	123	1111	0	123	234567	344547764	WIRELESS	READY	TYPE	23566	1
5	XXXX	123	1111	0	123	234567	344547764	WIRELESS	PERMIT	TYPE	2353	4562
6
7
Sheet2

 

[DFlem]

I am having trouble reconciling my response in Post #7- As I re-visit it, it would seem that this should be the correct result... Disregarding the Header Row.

Split text.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Work Type	M W 0#	M P O#	C WO#	CPO3	P NUM	TICKET NUM	CUSTOMER COL 2	READY/PERMIT 2	TYPE 2	SM 2	QUANTITY 1
2	XXXX	123	1111	0	123	234567	344547764	CAST	READY	TRANSFER	122348	1
3	XXXX	123	1111	0	123	234567	344547764	JIM	PERMIT	PULL	35674	1
4	XXXX	123	1111	0	123	234567	344547764	WIRELESS	READY	TYPE	23566	1
5	XXXX	123	1111	0	123	234567	344547764	WIRELESS	PERMIT	TYPE	2353	4562
6
7
Sheet2

haha right, the raw report structure is insane compared to the final report! Yes. your output for row 2 in your post #7 is my desired output for every row.

 

[igold]

To be clear, my Post #7 or my Post #12 is correct. In Post #7 I did not include the Name "Cast". In the cells that uniquely belong to the customers, is "Quantity" the first cell that goes with the Customer or the last cell that belongs to the unique Customer. Also if you could map your column headers to the correct post, it would helpful as your sample data does not seem to match. For Instance the second cell in the Header in Cell B1 is "Received Date" but your sample data has numbers that are not formatted as dates. I am very confused.

Perhaps you could do a small copy and paste mock up of the correct headers with the correct corresponding raw data from Row 1 of your provided sample Raw Data.

 

[DFlem]

To be clear, my Post #7 or my Post #12 is correct. In Post #7 I did not include the Name "Cast". In the cells that uniquely belong to the customers, is "Quantity" the first cell that goes with the Customer or the last cell that belongs to the unique Customer. Also if you could map your column headers to the correct post, it would helpful as your sample data does not seem to match. For Instance the second cell in the Header in Cell B1 is "Received Date" but your sample data has numbers that are not formatted as dates. I am very confused.

Perhaps you could do a small copy and paste mock up of the correct headers with the correct corresponding raw data from Row 1 of your provided sample Raw Data.

The only data in the uploads that I posted that is accurate are the headers in post #10. I just added stuff in the other columns as context due to this project being work related. The way that you separated the customers in post #12 with "cast" is correct.

 

[igold]

Is this getting close to your requirements...

Sub SplitRaw()

    Dim inp, erow(1 To 7), custs(1 To 5)
    Dim ct As Long, i As Long, lRow As Long, ct2 As Long, lCol As Long
    Dim r As Long, oPutlRow As Long, e As Long
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    
    Application.ScreenUpdating = False
    ws1.Activate
    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    lCol = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
    oPutlRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    For r = 2 To lRow
        i = 0
        inp = ws1.Range(Cells(r, 1), Cells(lRow, lCol))
            For e = 1 To 7
                erow(e) = inp(1, e)   'Generate first 7 static columns
            Next
            For ct = 1 To (UBound(inp, 2) - 7) / 5
                For ct2 = 1 To 5
                    custs(ct2) = inp(1, 8 + i)   'Generate unique customer data
                    i = i + 1
                Next
                ws2.Range("A" & oPutlRow).Resize(, 7) = erow
                ws2.Range("H" & oPutlRow).Resize(, 5) = custs
                oPutlRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
        Next
    Next
    Application.ScreenUpdating = True
  
End Sub