Hi,
I have a large data set (over 1,000 rows). There are multiple qualifiers (different columns of data) that are used to populate an end user report. I recently acquired this report and found the prior user was populating everything manually (by sorting the qualifiers, summing the cells and manually entering that total into the report!). I am trying to see if there is a way to create a nested formula (or perhaps macro) to consolidate the data based on the qualifiers. See attached an example of (a) the qualifiers (b) a subset of the data and (c) a copy of the report. What I am trying to do is perhaps create a nested IF, AND formula for the qualifiers to populate the report, but I'm unsure if Excel can handle how many nested formulas there would be and also wondering if there is a better way to go about this project. This report used to take the old user 3-4 business days to fully populate so I'm trying to streamline that process. Appreciate the assistance and let me know if any follow up questions.
Tab 1: these are the qualifiers - 4 different items that when combined produce a separate line on the report. Every line will feature a main account & report line item detail, but not every line will feature a sub-account or vendor name (as can be seen from the examples in the data subset and reporting).
Tab 2: this is an example of how the report appears and the different columns of data that are then turned into a report.
Tab 3: this is an example of the report - you can see the numbers in col C are hard-coded. The columns on tab 2 were manually filtered and amounts populated by summing the cells and hard coding the number in the cell.
Bookmarks