I am running into issues trying to get a data set in the preferred visual format for the folks I am supporting. I've messed around with just normal sorting, pivot tables, and creating a hierarchy in power pivot. My VBA skills are at the level of googling code for what i am attempting to do but I didn't come up with anything that worked for this case.

Below is some dummy data I threw together. The actual data has dozens more columns with data I would have to the right of the desired output. It's all coming from a query with 25k rows and items get added & dropping off throughout the day.

It could go a few layers deeper than this but for a visual, 4 felt plenty. Ultimately I am trying to take the data in columns A:F and have it generate the end result in column I:J which I did manually here for illustration. The helper column is just to get the right level of indentation, The color coding was just a visual aid for myself to create the dummy data.

I feel like this is a simple thing and I am just missing something stupid. Any help is greatly appreciated!

example data.xlsb