Data Analysis Question

2.82K viewsFormulas and Functions

Attached is a model containing a hypothetical quickbooks export of customer data by year by revenue type.

The goal of the model is to pull customer data from 3 periods (spreadsheets copied and pasted into matrices) into one matrix with a year category. You’ll notice that the Customer items are not consistent from period to period so there needs to be some sort of key to make a master customer list for the analysis.

Can anyone give me some guidance on how to structure the model and what formulas to use to pull the customer data into the analysis matrix?




Hi – here is one approach. You have to write a formula for each year but I think it is reasonable. It uses the USING AS functionality along with an associated lookup table for the ‘master companies’.

You are viewing 1 out of 1 answers, click here to view all answers.