VBA Help - Creating Data Table from Dashboard

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
679
Office Version
  1. 2016
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOPQRSTUV
1210/4/1910/11/1910/18/1910/25/1911/1/1911/8/1911/15/1911/22/1911/29/1912/6/1912/13/1912/20/1912/27/19
13SeriesIterationCategorySeries StartSeries EndSeries ValidationHow Many values on 1st Drop?Total ValuesRun Time (min)4111825181522296132027
14Sample 11Cat 111/1/1912/20/19Good310601-345678910Sample 1
15Sample 21Cat 21/25/1912/13/19Good310601-345678910Sample 2
16Sample 31Cat 31/17/203/13/20Invalid Dates1860
Sample


Sample Extract
Sample File.xlsm
ABCDEFGHIJK
1SeriesIterationCategorySeries StartSeries EndSeries ValidationHow Many values on 1st Drop?Total ValuesRun Time (min)DatesValue
2Sample 11Cat 111/1/1912/20/19Good3106011/1/191-3
3Sample 11Cat 111/1/1912/20/19Good3106011/8/194
4Sample 11Cat 111/1/1912/20/19Good3106011/15/195
5Sample 11Cat 111/1/1912/20/19Good3106011/22/196
6Sample 11Cat 111/1/1912/20/19Good3106011/29/197
7Sample 11Cat 111/1/1912/20/19Good3106012/6/198
8Sample 11Cat 111/1/1912/20/19Good3106012/13/199
9Sample 11Cat 111/1/1912/20/19Good3106012/20/1910
10Sample 21Cat 21/25/1912/13/19Good3106010/25/191-3
11Sample 21Cat 21/25/1912/13/19Good3106011/1/194
12Sample 21Cat 21/25/1912/13/19Good3106011/8/195
13Sample 21Cat 21/25/1912/13/19Good3106011/15/196
14Sample 21Cat 21/25/1912/13/19Good3106011/22/197
15Sample 21Cat 21/25/1912/13/19Good3106011/29/198
16Sample 21Cat 21/25/1912/13/19Good3106012/6/199
17Sample 21Cat 21/25/1912/13/19Good3106012/13/1910
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
679
Office Version
  1. 2016
Platform
  1. MacOS
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.


VBA Code:
'----------------------------------------------------------------------------------------------------------
'--- 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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,576
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top