Merging duplicate rows
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Merging duplicate rows
 

Guess not!

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Merging duplicate rows
 

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))


--
Regards,
Tom Ogilvy


mattis2k wrote in message
...

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 it
lists the model, description and quantity.

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

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

can anyone help..?

Matt


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Merging duplicate rows
 

Cheers Tom,

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

Thanks

Matt


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Merging duplicate rows
 

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

--
Regards,
Tom Ogilvy


mattis2k wrote in message
...

Cheers Tom,

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

Thanks

Matt


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/