VBA to populate a Summary based on a Matrix of Variables

[Johnny Thunder]

Hello All,

I am working on a project that has a summary sheet with some finance categories (see sample data below) using a matrix I have defined what Cost Centers and G/L Accounts​ are used to calculate the categories total.
This is simple to do on a 1 cost center & 1 G/L Account basis using a SumProduct Formula since I am able to do 3 criteria within the formula to get the desired result. The problem I am having is that for some of the categories there may be multiple variables needed to get to 1 total.

My Vision: I am hoping to have code that will do a For Each Statement on the summary sheet Column A, for every non-blank (i.e Marketing, Publicity ect.) grab the value in Column A, Reference the Matrix Sheet and pass the variables to build the total needed.

Option1 (AutoFilter): I tried going the route of using code to pass in the Cost Center/GL Account into the Criteria of an AutoFilter but I was unsuccessful in this attempt. In theory, what I was hoping to do is if the Category had "All" listed in either Cost Center or G/L Account, it essentially means I don't need to use that field since I want every possible Cost Center or G/L Account so Ignore the "All Comment and move to the next variable and apply that variable to the Filter and then Sum the value present on the filtered data. This seemed like the best method of doing what I need but my knowledge of the Autofilter command syntax isn't the best and the macro recorder wasn't much help.... the biggest issue I encountered was, if my Criteria was solely on G/L Account for example, I could define a variable like "rng" as the range of cells on the Matrix tab and pass those to the Criteria field, I found code online that showed this was possible but for whatever reason I couldn't get it to work.

Option2 (Staging Sheet): Another idea I thought about was to do the same lookup as Option1 but instead of using the AutoFilter, why not apply a formula, and paste the results into a hidden Staging Sheet and once all variations of the Category (i.e Marketing) have been applied, then Sum the formulas up and then drop in that total into the Summary sheet. This was another route that I tried but I kept hitting a wall with using a loop to go left to right within one row as opposed to going up and down in a column, this loop would have to be dynamic enough to 1st take into account all the variations for Cost Center from the Matrix Sheet, then apply the G/L Account variations to ensure no combinations are missed.

Option3 (Scripting Dictionary: Same idea as Option2 except instead of having a Sheet "Staging" just pass the results to a Scripting Dictionary and upon completing all variations of the Cost Center/ GL Account lookups from the Matrix Sheet, then Summarize all the results and pass them to the specific line item on the Summary Sheet and then Clear the Dictionary and move onto the next Category.


Below I have added some screens so you can visualize the type of data I am working with. this of course is just a small subset of data to get a general idea of what I need. For the purposes of the code and sample, I am only working with one quarter at a time so Q2 2019 is the most relevant for the below sample data.

Any help is appreciated on this project. I've been spinning my wheels for 3 days now and haven't made much progress. If you have any questions are want a more thorough explanation on something please don't hesitate to ask.

Thanks!



*For the Matrix Tab, the idea was to build a Matrix for Cost Center and G/L Account that had about 20 Place Holders for future variations, at the moment we are only using 3 per field just as the example of the table below shows but I would need the code to be able to look at the values that are present and always include new values if they appear.

	A	B	C	D
Catergory
Q1 2019	Q2 2019	Q3 2019
Grand Total

<tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]Period[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00"]Marketing[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFF00"]Publicity[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFF00"]Residuals[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]
[TD="align: right"] -[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary

	A	B	C	D	E	F	G	H
Residuals
Cost Center	All
GL Account
Marketing
Cost Center
GL Account
Publicity	Cost Center
GL Account	All

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Order[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Category[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Count>>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] , align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]500000[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1588[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]500000[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]3000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Matrix

​

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Cost Center[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]GL Account[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Q1 2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Q2 2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Q3 2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5B9BD5]#5B9BD5[/URL] "]Q4 2019[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1588[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"] $ 100.00[/TD]
[TD="align: right"] $ 52.50[/TD]
[TD="align: right"] $ 60.50[/TD]
[TD="align: right"] $ 15.55[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1555[/TD]
[TD="align: right"]600000[/TD]
[TD="align: right"] $ 200.00[/TD]
[TD="align: right"] $ 60.50[/TD]
[TD="align: right"] $ 22.00[/TD]
[TD="align: right"] $ 66.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]700000[/TD]
[TD="align: right"] $ 300.00[/TD]
[TD="align: right"] $ 75.20[/TD]
[TD="align: right"] $ 323.00[/TD]
[TD="align: right"] $ 22.70[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]800000[/TD]
[TD="align: right"] $ 400.00[/TD]
[TD="align: right"] $ 33.17[/TD]
[TD="align: right"] $ 500.00[/TD]
[TD="align: right"] $ 150.17[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"] $ 500.00[/TD]
[TD="align: right"] $ 44.00[/TD]
[TD="align: right"] $ 17.00[/TD]
[TD="align: right"] $ 110.34[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1588[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"] $ 400.00[/TD]
[TD="align: right"] $ 33.17[/TD]
[TD="align: right"] $ 500.00[/TD]
[TD="align: right"] $ 150.17[/TD]

</tbody>

Data

 

[Johnny Thunder]

Bump