VBA Help - Creating Data Table from Dashboard

[Johnny Thunder]

Hello All,

I have a dashboard that I need to extract data from to turn into a vertical style report to be able to run pivots and other things with the data.

From the dashboard, I need to grab the headers from Cells (A13:I13) and then get the details starting at row 15. For any Series value that has a Series Validation that equals "Good" then get the numeric values that are in columns J:End of Report (Sample data ends at Column V) and get the corresponding date where the value was pasted from row 12. I do not need the Series name that is at the end of the dates, this was more of a visual thing, so I just need the Numeric Values and the corresponding dates in the table from columns J:V (V is the current end of the sample, actual data is much longer)

My Dashboard

Sample File.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
12										10/4/19	10/11/19	10/18/19	10/25/19	11/1/19	11/8/19	11/15/19	11/22/19	11/29/19	12/6/19	12/13/19	12/20/19	12/27/19
13	Series	Iteration	Category	Series Start	Series End	Series Validation	How Many values on 1st Drop?	Total Values	Run Time (min)	4	11	18	25	1	8	15	22	29	6	13	20	27
14	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60					1-3	4	5	6	7	8	9	10	Sample 1
15	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60				1-3	4	5	6	7	8	9	10	Sample 2
16	Sample 3	1	Cat 3	1/17/20	3/13/20	Invalid Dates	1	8	60
Sample

Sample Extract

Sample File.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Series	Iteration	Category	Series Start	Series End	Series Validation	How Many values on 1st Drop?	Total Values	Run Time (min)	Dates	Value
2	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	11/1/19	1-3
3	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	11/8/19	4
4	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	11/15/19	5
5	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	11/22/19	6
6	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	11/29/19	7
7	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	12/6/19	8
8	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	12/13/19	9
9	Sample 1	1	Cat 1	11/1/19	12/20/19	Good	3	10	60	12/20/19	10
10	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	10/25/19	1-3
11	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	11/1/19	4
12	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	11/8/19	5
13	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	11/15/19	6
14	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	11/22/19	7
15	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	11/29/19	8
16	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	12/6/19	9
17	Sample 2	1	Cat 2	1/25/19	12/13/19	Good	3	10	60	12/13/19	10
Data Dump

Here is what the desired result would look like. The series value repeated with every iteration of Numeric value and Corresponding date. If the Series Validation for that row = "Invalid Dates" then skip the entire row.
Highlighting was to make it easier for anyone helping to see where one values starts and ends.

Any ideas are appreciated. Thanks!

 

[Johnny Thunder]

Ok, I was able to get to this point from using a macro from another project but now I am hitting a 2 roadblocks. Any suggestions are appreciated.

#1 For some reason my LastCol Declaration for last column goes out to 139 Columns which is correct, but when the array code finishes it is stopping at column 26? Not sure why?
#2 When the code gets the row data from my dashboard any value that has a dash like "1-3" gets converted to a date when the output gets populated on my Data Dump Sheet, my knowledge is limited on how to fix this issue.

'----------------------------------------------------------------------------------------------------------
'--- Extracts Timeline Data and Creates Linear Report
'----------------------------------------------------------------------------------------------------------
Sub Creating_Report()
  
Dim ws1                   As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim a                        As Variant, b As Variant
Dim i                         As Long, j As Long, k As Long, LastR As Long, LastCol As Long, StartPosition As Long, LastR1 As Long, LastR3
Dim wKstNames     As Variant, Wksht As Variant

Set ws1 = Sheets("Timeline")
Set ws2 = Sheets("Data Dump")
 
LastR = ws1.Range("C" & Rows.Count).End(3).Row     'last row of column O
LastCol = ws1.Cells(13, Columns.Count).End(1).Column  'last column of row 2
    
                  'Load in memory all data from cell O1 and to the last cell with data
                  a = ws1.Range("A1", ws1.Cells(LastR, LastCol)).Value2
                                
                  'Calculate an output matrix (1 to rows, 1 to columns) - the number of rows will be equal to the number of cells from cell O1 to the last cell - With 4 columns
                  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 11)
                  
                  For i = 15 To UBound(a, 1)      '7 initial row
                    
                    If a(i, 1) <> "" Then
                      
                      For j = 10 To UBound(a, 1) 'Column 10 is the start of the dates
                        If a(i, j) <> "" Then
                          k = k + 1
                          b(k, 1) = a(i, 1) 'Series
                          b(k, 2) = a(i, 2) 'Iteration
                          b(k, 3) = a(i, 3) 'Category
                          b(k, 4) = a(i, 4) 'Series Start
                          b(k, 5) = a(i, 5) 'Series End
                          b(k, 6) = a(i, 6) 'Series Validation
                          b(k, 7) = a(i, 7) 'How many Ep on Drop
                          b(k, 8) = a(i, 8) 'Total Values
                          b(k, 9) = a(i, 9) 'Run-Time
                          b(k, 10) = a(i, j) 'Series Values
                          b(k, 11) = a(12, j) 'Series Date for values
                        End If
                      Next j
                    End If
                  Next i
              
            'Determines last row on Data Dump Sheet Before dropping in the array data
           StartPosition = ws2.Range("A" & Rows.Count).End(xlUp).Row
              
            ws2.Range("A" & StartPosition + 1).Resize(k, 11).Value = b   'Drops in values on Data Dump