How Do I Quickly Re-state Values with VBA?

Being a Reporting Analyst for a huge enterprise often means that there might be no single centralised data warehouse where all enterprise data resides but rather a number of business intelligence systems, each of which tackles a different set of business operations data. Thus, you get one system for your sales analysis, another for P&L reporting, and yet a third one for all supply chain data.

Yet, should you happen to be the analyst supporting the regional management, it is highly likely that you might have to show a number of sales, financial and supply chain KPIs in the same high-level overview scorecard.

And that’s where challenge # 1 ensues: measure units between systems are not the same and the only tool you have to address this is Excel. Of course you can tackle the problem by using some extra formulas that will divide (or multiply) as neeeded.

However, it’s much better for the efficiency of your spreadsheet if you have this as a step in your data prep model

Have you ever had to combine data where numbers were stated in different measure units – e.g. actuals in millions and budgets in thousands?

• Did you ever have to convert expenses to negative numbers?

If yes, you might find the VBA snippet below a handy trick to have up your sleeve.

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.