Merging duplicate rows

[mattis2k]

Hi,

Firstly I'd like to thank the people on here for replying so quickly
its a great facility

I have a problem with a order worksheet in my workbook..

basically, it allows products to be added into a summary, where i
lists the model, description and quantity.

What I want is to merge the duplicate rows, and increment the quantit
field.

So if i have 3 rows the same, it will filter to one row and put "3" i
the quantity field..

can anyone help..?

Mat

 

[mattis2k]

Guess not!

hmm, can anyone give me an inclin on how to start the task

 

[Tom Ogilvy]

You can use the Data=>Filter=>Advanced filter, checking the uniques checkbox
and copy to another location to get a list of your uniques. If you start
from the destination sheet, you can have the data filter put the information
on another sheet. You need to specify the three columns that form the
unique data.

You can then put in a sumproduct formula next to these unique entries to sum
up.

=sumproduct(--(Sheet1!$A$1:$A$200=A1),--(Sheet1!$B$1:$B$200=B1),--(Sheet1!$C
$1:$C$200=C1),$D$1:$D$200)

then drag fill it down the list.

If you just want a count of the rows (which is what you described), remove
the last argument.

=sumproduct(--(Sheet1!$A$1:$A$200=A1),--(Sheet1!$B$1:$B$200=B1),--(Sheet1!$C
$1:$C$200=C1))

 

[mattis2k]

Cheers Tom,

But I was really looking for a way in VB, sorry i forgot to mention
that bit....

Thanks

Matt

 

[Tom Ogilvy]

It is even easier in VBA to call the data filter and put the results on a
separate sheet.

Adding the sumproduct formula to the cells in VBA would again be simplicity.


Sub AAAD()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
With Worksheets("NC90")
Set rng = .Range("A1"). _
CurrentRegion.Resize(, 3)
End With
Set rng2 = rng.Offset(1, 0). _
Resize(rng.Rows.Count - 1, 1)
With Worksheets("sheet3")
rng.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("A1:C1"), _
Unique:=True
Set rng1 = .Range(.Cells(2, 1), _
..Cells(Rows.Count, 1).End(xlUp))
End With
rng1.Offset(0, 3).Formula = "=SumProduct(--(" & _
rng2.Address(External:=True) _
& "=A2),--(" & rng2.Offset(0, 1).Address(External:=True) _
& "=B2),--(" & rng2.Offset(0, 2).Address(External:=True) _
& "=C2))"
rng1.Offset(0, 3).Formula = rng1.Offset(0, 3).Value
End Sub