VBA to populate a Summary based on a Matrix of Variables

Johnny Thunder

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

ABCD
3CatergoryPeriod
4Q1 2019Q2 2019Q3 2019
5Marketing - - -
6
7Publicity - - -
8
9Residuals - - -
10Grand Total

<tbody>
</tbody>
Summary

ABCDEFGH
2OrderCategoryCount>>12345
31Residuals
4Cost CenterAll
5GL Account500000600000800000
6
72Marketing
8Cost Center15881800
9GL Account500000700000900000
10
113PublicityCost Center30004000
12GL AccountAll

<tbody>
</tbody>
Matrix


ABCDEF
1Cost CenterGL AccountQ1 2019Q2 2019Q3 2019Q4 2019
21588500000 $ 100.00 $ 52.50 $ 60.50 $ 15.55
31555600000 $ 200.00 $ 60.50 $ 22.00 $ 66.00
41800700000 $ 300.00 $ 75.20 $ 323.00 $ 22.70
53000800000 $ 400.00 $ 33.17 $ 500.00 $ 150.17
64000900000 $ 500.00 $ 44.00 $ 17.00 $ 110.34
71588900000 $ 400.00 $ 33.17 $ 500.00 $ 150.17

<tbody>
</tbody>
Data
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,141,413
Messages
5,706,298
Members
421,440
Latest member
cmphares

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