PDA

View Full Version : Consolidate and remove duplicate



snoom82
07-21-2016, 06:53 AM
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
16696


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
07-21-2016, 10:41 AM
It's not clear what you want to total, but if you wish to group Customer, could you use a pivot table?

snoom82
07-21-2016, 01:31 PM
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
07-21-2016, 01:41 PM
If a pivot table can give you the results you are after, you can copy/pastespecial into the desired layout and delete the pivot