Consolidate and remove duplicate

[snoom82]

Hi,

I am trying to find a solution in order to consolidate based on column C, and delete the duplicated rows based on the same column.

What I started to do is maybe a complicated way. Basically I tried to select the current region and offset it in order to select from column C, set this selection as a named range, select the 5th empty cell row C and consolidate the named range (which does not work and send me the error 1004)

Sub consolidate()
Dim test As Range




Range("A3").Select
Selection.CurrentRegion.Select
Selection.Offset(2, 2).Select
Set test = Selection


LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 5
Range("C" & LastRow).Select
Dim DataRange As Range




Selection.consolidate Sources:= _
Range("test"), Function:= _
xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False


End Sub '


The problem is I didn;t manage to make the consolidation work, and I am far from being able to start removing the duplicate rows...

is anyone has a better idea? I am sure there is a better and easier solution, my VBA level doesn;t allow me to find it...
the excel layout is as below

Column A
macrotest.xlsx

	21/07/2016					Week	29
Material	Description	Customer	Teteghem Stock	In Transit	UK Stock	Call Off	Production
***A	material A	20021593	9,337	8,947
***E	material E	20028448	232,622
***E	material E	20028448	7,391	161,046	292,139
***E	material E	20028448	14,782		17,894		393,668
***D	material D	20021438	8,943
***E	material E	20023371	249,738	16,727
***A	material A	20023363	751,548	105,029
***B	material B	20020682	2,374,456	32,290
***A	material A	20022901	572,608
***B	material B	20027870	7,390
***E	material E	20028448	232,233
***E	material E	20028448	53,682
***E	material E	20028448	4,279
***E	material E	20028448	166,492

[mdmackillop]

It's not clear what you want to total, but if you wish to group Customer, could you use a pivot table?

[snoom82]

Yes, the idea is to group customer and sum up the volume by column at the right of customer (D, E, F, G, H). For a same customer, material and description will be the same and the number of customer can vary.

This consolidation is actually part of a much bigger macro I have done, and I had like to automatise the consolidation / remove duplicate part. I wouldn't use a pivot table to be honest.

[mdmackillop]

If a pivot table can give you the results you are after, you can copy/pastespecial into the desired layout and delete the pivot