Hello,
I would like to run a VBA routine that takes a data table with unique records and creates a single list with multiple subtotals rows (and excel groupings, if possible), based on the commonalities. I bet this already exists out there somewhere, but I couldn't figure out a good search term to find a useful post. Can anyone help me out?
Example current data:
<tbody>
</tbody>
Example format after macro (ignore third column; just a comment column for this post):
<tbody>
</tbody>
This is an incredibly simplified version of what I'm working with. Not all people would have food/beverage; not all people would have all regions; and there are many more columns and rows than this, but they all drill up from right to left. I thought of having the macro use all unique values through all levels (so copy and paste Food and Beverage for everyone, and USA and Canada for everyone), and then run a macro to remove all blank rows (based on lookup formulas I am using after the report formatted). That's an option, if it's easier to built the chart that way.
I just need this to build out the row descriptions; I will have lookup formulas for pulling the data from my table based on rebuilt label column.
Thoughts? I just can't wrap my mind around looping through unique records and positioning the subtotals appropriately.
Thanks in advance
I would like to run a VBA routine that takes a data table with unique records and creates a single list with multiple subtotals rows (and excel groupings, if possible), based on the commonalities. I bet this already exists out there somewhere, but I couldn't figure out a good search term to find a useful post. Can anyone help me out?
Example current data:
| Joe | Steve | Canada | Food |
| Joe | Steve | Canada | Beverage |
| Joe | Steve | USA | Food |
| Joe | Steve | USA | Beverage |
| Joe | Renae | Canada | Food |
| Joe | Renae | Canada | Beverage |
| Joe | Renae | USA | Food |
| Joe | Renae | USA | Beverage |
| Joe | Kyle | USA | Food |
| Joe | Kyle | USA | Beverage |
<tbody>
</tbody>
Example format after macro (ignore third column; just a comment column for this post):
| Canada | Food | Unique |
| Canada | Beverage | Unique |
| USA | Food | Unique |
| USA | Beverage | Unique |
| Steve | Food | Subtotal for Steve |
| Steve | Beverage | Subtotal for Steve |
| Canada | Food | Unique |
| Canada | Beverage | Unique |
| USA | Food | Unique |
| USA | Beverage | Unique |
| Renae | Food | Subtotal for Renae |
| Renae | Beverage | Subtotal for Renae |
| USA | Food | Unique |
| USA | Beverage | Unique |
| Kyle | Food | Subtotal for Kyle |
| Kyle | Beverage | Subtotal for Kyle |
| Joe | Food | Subtotal for Joe (includes all levels under Joe) |
| Joe | Beverage | Subtotal for Joe (includes all levels under Joe) |
<tbody>
</tbody>
This is an incredibly simplified version of what I'm working with. Not all people would have food/beverage; not all people would have all regions; and there are many more columns and rows than this, but they all drill up from right to left. I thought of having the macro use all unique values through all levels (so copy and paste Food and Beverage for everyone, and USA and Canada for everyone), and then run a macro to remove all blank rows (based on lookup formulas I am using after the report formatted). That's an option, if it's easier to built the chart that way.
I just need this to build out the row descriptions; I will have lookup formulas for pulling the data from my table based on rebuilt label column.
Thoughts? I just can't wrap my mind around looping through unique records and positioning the subtotals appropriately.
Thanks in advance
Last edited:
