Create Where-Used Report Hierarchially

A pivot table based on columns AO:AR on the Level 1 sheet comes close to what is shown on the Output - Hierarchy sheet.
I feel that the best way to know if a formula/pivot table based approach will be feasible is to test it on the actual data.
I don't know enough about VBA to say whether or not a macro would help with replicating the Output - Part sheet.
I had seen that this was a VBA question, but decided to reply to the thread when I saw it had gone two days without one.