Why use VBA when Excel's Dynamic Array Formulas do the heavy–lifting for you?...
I’ve been working on a small Excel project for a large FMCG (Fast Moving Consumer Goods) client.
In this case the project is a GRIS or Goods Receipt / Invoice Receipt. Essentially, we’re taking some ugly extracts from SAP (who designed these things?) matching the goods received from each supplier with the corresponding invoice received from that supplier.
It's basically a way to ensure that they’re only paying for what they actually received.
As mentioned, this involves a couple of text file extracts from SAP, one for the prior period and another for the current period.
The GRIR reconciliation needs to consolidate a unique list of account codes from each extract and use this as the basis for a report to compare the account balance from each period.
The SAP Extracts
The SAP extracts – after cleaning up and pasting into Excel – look the example below.
There’s two extracts: one for the current period and one for the prior period.
We need to combine the Account Numbers from both extract, get a unique list Account Codes and sort them.
In the past, to consolidate the list of account codes from both extracts, I would have written some VBA.
Perhaps automate Excel’s Advanced Filter feature or looped through each pasted extract using a Dictionary to collate a list of unique account codes.
Traditionally, these were the type of automation tasks VBA excelled at (no pun intended).
The GRIR Report
Here’s the report we need to produce (check out the carousel). It’s fairly simple.
It presents a unique, sorted list of Account Numbers from both SAP extracts and shows the account balance from both report periods and the difference.
The account name and classification are pulled from another tab which map account numbers to user defined classifications.
Again, in the past, given the structure of the data across multiple tabs, we may have resorted to VBA to consolidate and aggregate the data.
The Dynamic Array Formula
Using Dynamic Array Formulas (or DAFs) a whole swathe of VBA code has been rendered redundant.
Here, 3 simple Dynamic Array functions build the framework for our report:
=SORT(UNIQUE(VSTACK(PriorTB[Account Number], AfterTB[Account Number])))
The VSTACK function tells Excel to grab the Account Numbers from the two Trial Balances and stack them one of top of each other.
The UNIQUE function, then filters those stacked Account Numbers so there are no duplicates.
And finally, SORT, well… sorts the list of Account Numbers for us.
Conclusion
Three functions, zero VBA code.
As you start to become more familiar with Excel’s library of Dynamic Array Formulas you’ll start to think of them in a modular fashion, like Ikea furniture or Lego bricks that you can mix-and-match to get meaningful results fast (and without VBA).
What’s you best example of Dynamic Array Formulas saving you time and getting great results fast? Do you have a favourite DAF?
0 comments