Copy and consolidate data from one sheet to another in the same workbook.

[madmoo84]

Hi everyone,
I am hoping you can help, I am trying to make a sort of order tracker sheet.
It has two tabs, the data and then the tracker. The data will simply be copied and pasted from another system but I would like the tracker tab to consolidate the info.

I have attached an example sheet that I have manually done.

The customer ref has a suffix of BN, BE or ME (sometimes BB or nothing). One order may have multiple lines on the data sheet as the order number will end in one of the suffixes. I need the pallet qty to be listed in the relevant column (rounded up to the nearest whole number i.e 0.2 becomes 1.

I need it so only the order number (not suffix) is listed along with the delivery point and postcode and then the pallet qty listed based on the suffix data all on one line as opposed to several lines for the same delivery point and order number.

Can this be done?

Thanks