Changing a single point of data into a row of data merging identical items into total…

Yeah that thread title even confuses me! (Short title, my boss is making my life harder by being stupid)

So here’s my enigma…
I do a manual weekly stock check on parts, the result is a list of approx. 100 locations and the parts in them. The end result is a 100+ line report with parts listed by number and includes number of pallets and up to 5 (usually 1) part full pallets. Parts can be on more than one line if they are in more than one location. There is potential for up to 5 part full pallets to be present for each part but usually only one.
This system need to be kept as is as it’s essentially a directory of where the parts are located. (see component stock check sheet for example)

My report is great I have total pallets per part in a single data point, however…

The system my boss uses has each part on a single line and (stupidly in my opinion) each pallet has its own column. I’m now supposed to include in my excel doc a page with the data in his format.
I’ve been trying to setup a page that works all the calculations needed (mostly via nested ifs) will probably need a few Vlookups etc This isn’t the issue. The problem I’m having, well two actually, is firstly combining a part on 2, 3 maybe even 4 lines in my stock report into one line that I can then automatically fill in his daft pallet section secondly I’m having trouble shortening the nested ifs to include all possible part pallets and all pallets.

I’m using a third sheet to do the working out then will paste the data into the required sheet and save just the first two pages.

The data to the left of his pallet sheet is grabbed from an internally linked excel doc that can change weekly so parts can move up or down the sheet.