VBA to format multiple columns and rows with shared attributes into single row with subtotals

[Gimics]

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:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Joe[/TD]
[TD]Steve[/TD]
[TD]Canada[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Steve[/TD]
[TD]Canada[/TD]
[TD]Beverage[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Steve[/TD]
[TD]USA[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Steve[/TD]
[TD]USA[/TD]
[TD]Beverage[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Renae[/TD]
[TD]Canada[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Renae[/TD]
[TD]Canada[/TD]
[TD]Beverage[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Renae[/TD]
[TD]USA[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Renae[/TD]
[TD]USA[/TD]
[TD]Beverage[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Kyle[/TD]
[TD]USA[/TD]
[TD]Food[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Kyle[/TD]
[TD]USA[/TD]
[TD]Beverage[/TD]
[/TR]
</tbody>[/TABLE]

Example format after macro (ignore third column; just a comment column for this post):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Canada[/TD]
[TD]Food[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]Beverage[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Food[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Beverage[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Food[/TD]
[TD]Subtotal for Steve[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Beverage[/TD]
[TD]Subtotal for Steve[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]Food[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]Beverage[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Food[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Beverage[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]Renae[/TD]
[TD]Food[/TD]
[TD]Subtotal for Renae[/TD]
[/TR]
[TR]
[TD]Renae[/TD]
[TD]Beverage[/TD]
[TD]Subtotal for Renae[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Food[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Beverage[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]Kyle[/TD]
[TD]Food[/TD]
[TD]Subtotal for Kyle[/TD]
[/TR]
[TR]
[TD]Kyle[/TD]
[TD]Beverage[/TD]
[TD]Subtotal for Kyle[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Food[/TD]
[TD]Subtotal for Joe (includes all levels under Joe)[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Beverage[/TD]
[TD]Subtotal for Joe (includes all levels under Joe)[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Gimics]

Bump; any takers? Or anyone heard of something similar and can refer me to a search term/other posting?