transform irreguler data with vba?

dear all,

i'm having this problem with legacy data (i believe it's a legacy export from crystal report, and the support team has been disbanded).

essentially need to transform data from sheet data into format like in sheet wanted.

the tricky part is one branch can have multiple ARM codes, which can have multiple account (and it self can have multiple project numbers).

i did a messy vba code at work laptop, quite able to solve one-to-one only, but not able to figure out the unknown multiple pattern.

pls find attached, i hope my explanation and the file can be well understood. THe attached is only a few rows of what i have (of hundreds, if not thousands), kinda hard to manually copy-paste into the desired format/structure.

many thanks for the help, any suggestions/pointers would be very much appreciated. cheers,

apologies. attached is the more 'complete' structure .

thanks for any pointers / help