Morning.

I have attached the Raw DATA which has had unnecessary rows removed, I need to remove duplicate lines of product while at the same time Suming the total units for each product, I need to be able to apply this to the entire sheet all at once. i have tried the consolidate functions in excel however these don't seem to work.

i.e.
product 42494 - Ref SB-519338 - Qty 3
product 42494 - Ref SB-519338 - Qty 6

changed to
product 42494 - Ref SB-519338 - Qty 9

this is because this information is then pasted into a sheet that breaks down the pack sizes and returns the total picks, this means if i have more than 1 line for the same product on the same order the total picks will be incorrect as the sheet will work out the 3 and then the 6 rather than working out 9.

i.e. the above might come in a pack of 9 so the sheet will return 3 inner picks for line 1 and 6 inner picks for line 2 rather than 1 full carton pick.



I hope I have made it clear enough for you to be able to help.

Thank you heaps.

Attached Files

Raw DATA.xlsx (217.8 KB, 15 views)

Is it like the attached?

Attached Files

Raw DATA_2.xlsx (324.3 KB, 12 views)

Thank you, I have applied it to the sheet and have changed the cell it is working off however I do not think it is fully working please see attached.

There are cells returning a Zero instead of 1 or 2 etc

also as this formula essentially creates a new units column is it possible to have it return the sum as well as the individual numbers of the other cells or a zero if it has been summed? please see below.

i.e.

12345 - units 2 - count 0
12345 - units 5 - count 7
58244 - units 1 - count 1
48125 - units 9 - count 0
48125 - units 9 - count 18

Attached Files

RAW DATA.xlsx (397.4 KB, 15 views)