Duplicate info from different columns transferred together into a different sheet

[el3nitr]

Hello,

I'm new so here please accept my apologies, if something just doesn't make sense I will try and be as clear as possible.

I have stumbled across an issue and i cant seem to figure out a way to resolve it.
On the below picture (First picture) you will see information that has been added automatically (white cells) and manually (yellowish cells). There is one column called "Pallet No" under each size that is visible on the below picture.

picture 1.PNG

Now this is where is gets a bit complicated as the issue is how do i transfer this information on my other sheet "Pallet View" as some boxes that have a different size product are one the same pallet.
For example all size small are on pallet 1 however there is some medium and large boxes that are on pallet 1 as well.

Below is a picture of how the "Pallet View" sheet is set up.

picture 2.PNG

Any help is greatly appreciated!!
I open to suggestions as well if I need to restructure the spreadsheet!!

Regards
George

[tim201110]

=IFERROR(INDIRECT($A$6&TEXT(AGGREGATE(15,6,(ROW(RNG)*10000+C$9)/(COLUMN(RNG)=D$9)/(RNG=D$10),ROWS(C$23:C23)),"R0000C0000"),),"")
with helper row