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:

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

 

[Gimics]

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