VBA Help - Build Report from Horizontal Data

[Johnny Thunder]

Hi Everyone,

I am working on a project that takes manually entered data in a horizontal layout and converts into a vertical data table. I have already built several things in the workbook to automate the process of data entry but am now stuck on the portion that will convert the data into a more vertical style data table.

Here is what the current report looks like:

PreRoll Working File V1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Week To Copy:	Friday, May 15, 2020
2
3												PreCheck 1	PreCheck 1	PreCheck 1	PreCheck 1		PreCheck 2	PreCheck 2	PreCheck 2	PreCheck 2
4		Comment:	Example Comment: 4th of July Coming Soon									PreCheck 1					PreCheck 2
5	Line	Order No	Product	Seasonal	Count	Start Date	Product Type	Class	Pre Awareness	Sustain length in days	End Date	Promo	Seasonal	Action	Date		Promo	Seasonal	Action	Date
6	1	100001	Wrench	S01	01	11/1/20	Hand Tools	Consumer	Skip	28	11/29/20	Summer Sale	Summer	25% Sale	11/15/20			Fall	Clearance Sale	11/20/20
7	2	100002	Hammer	S01	02	11/1/20	Hand Tools	Consumer	Skip	28	11/29/20							Spring	Clearance Sale	11/21/20
8	3	100003	Drill	S01	03	11/1/20	Hand Tools	Consumer	Skip	28	11/29/20	Home Improvement		Display	11/7/20		Tool Time		Banner	11/7/20
9	4	100004	Tool Belt	S01	04	11/8/20	Hand Tools	Consumer	Skip	28	12/6/20						Toolman	Winter	Display	11/30/20
Mock Data

For the sample data my PreChecks end at #2 but my working file goes out to #5 so will need to factor that in with any suggestions made.

What I am hoping to achieve is to Paste the values from Column A:K in a new Sheet "Mock Table" and append the Precheck Comments to the right of the data and also include the Precheck number at the end of the pasted data. There are occasions where there is more than one PreCheck that happen on the same row which is why I need to spin the data into a more vertical style table (see below example).

Here is what I am hoping the new Table would look like. You can see in the example Row 2 And 3 have the same details from Columns A:K but the PreCheck data was from two different numbers. This is what I am hoping to achieve. Also, in the event that there is no data in the Precheck fields the code will skip that section and move to the next. Any ideas are appreciated. ?

PreRoll Working File V1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Line	Order No	Product	Seasonal	Count	Start Date	Product Type	Class	Pre Awareness	Sustain length in days	End Date	Promo	Seasonal	Action	Date	Check Status
2	1	100001	Wrench	S01	01	44136	Hand Tools	Consumer	Skip	28	11/29/20	Summer Sale	Summer	25% Sale	11/15/20	PreCheck 1
3	1	100001	Wrench	S01	01	44136	Hand Tools	Consumer	Skip	28	11/29/20		Fall	Clearance Sale	11/20/20	PreCheck 2
4	2	100002	Hammer	S01	02	44136	Hand Tools	Consumer	Skip	28	11/29/20		Spring	Clearance Sale	11/21/20	PreCheck 2
5	3	100003	Drill	S01	03	44136	Hand Tools	Consumer	Skip	28	11/29/20	Home Improvement		Display	11/7/20	PreCheck 1
6	3	100003	Drill	S01	03	44136	Hand Tools	Consumer	Skip	28	11/29/20	Tool Time		Banner	11/7/20	PreCheck 2
7	4	100004	Tool Belt	S01	04	44143	Hand Tools	Consumer	Skip	28	12/6/20	Toolman	Winter	Display	11/30/20	PreCheck 2
Mock Table

 

[DanteAmor]

Try this

Sub Build_Report()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, lr As Long, lc As Long, m As Long
  
  With Sheets("Mock Data")
    lr = .Range("A" & Rows.Count).End(3).Row
    lc = .Cells(5, Columns.Count).End(1).Column
    a = .Range("A4", Sheets("Mock Data").Cells(lr, lc)).Value2
  End With
  ReDim b(1 To UBound(a) * Int((lc - 11) / 4), 1 To 16)
  
  For i = 3 To UBound(a, 1)
    For j = 12 To UBound(a, 2) Step 5
      If a(i, j) & a(i, j + 1) & a(i, j + 2) & a(i, j + 3) <> "" Then
        m = m + 1
        For k = 1 To 11
          b(m, k) = a(i, k)
        Next k
        b(m, 12) = a(i, j)
        b(m, 13) = a(i, j + 1)
        b(m, 14) = a(i, j + 2)
        b(m, 15) = a(i, j + 3)
        b(m, 16) = a(1, j)
      End If
    Next j
  Next i
  
  Sheets("Mock Table").Range("A2").Resize(m, 16).Value = b
End Sub

 

[Johnny Thunder]

That was amazing! Worked exactly how I needed it.

I am still super confused on how these style of macros work but they work great!

Thanks again Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.