Finally got Day #7 done! This was honestly awful and took way longer than any human should spend on a workflow.
My solution is not my most elegant. And I'm morally opposed to repeated folder names in the nesting.
what a mess - this took WAY longer than it should have - I was stuck on a bad RegEx path and had to abandon that.
Dirty solution below
This creates a folder for every file - and then duplicates each file for every level of parent.
Much more painful than it needed to be
Part 2:
Just took the working data from part 1 and popped them into a different summary. Once I'd done part 1 - part 2 was a breeze.
My solution.
I am not good at macros, but this time it worked.
Main
Multi-Row Formula to get Path
IF [Field_1] = "$ cd /"
THEN ""
ELSEIF [Field_1] = "$ cd .."
THEN RegEx_Replace([Row-1:Path], "(.*/).*/", "$1")
ELSEIF StartsWith([Field_1], "$ cd ")
THEN [Row-1:Path] + Replace([Field_1], "$ cd ", "") + "/"
ELSE [Row-1:Path]
ENDIF
Macro to get total size of files under a Path
I didn't use a macro! Mostly because I was copy and pasting a set of tools to test my logic before building the macro, once I'd tested 4 levels it was one copy and paste and as much clicking as making the iterative macro to get to the answer. Already lost hours to it so stormed ahead with the "it ain't pretty but it works" approach, as with most of my AoC attempts. Stil. Got stars and that's what we're here for. Will return and do the macro properly soon...
Thanks @balders-ie's hint, FileGetDir() is a life changer. Part 2 confused me quite a bit, after reading the questions over and over again, just used common sense for it.
Not only a brilliant use of ReverseString but also looping through it using the generate rows! Love it.