vb SUM OF DUPLICATES

[milleroy]

Hi I really need some help with this one - Please !

I have a table with 5 columns.
I want to use VB to look for a duplicate in column D, when one (or more) is found, add up the duplicates in column E and delete the unwanted rows. As in example

column D column E
Prod 1 2
Prod 2 3
Prod 3 56
Prod 3 44
Prod 3 10
Prod 1 33
Prod 1 67

What I would like it to show is

column D column E
Prod 1 2
Prod 2 3
Prod 3 110
Prod 1 100

Any suggestions ?

[Tom Ogilvy]

Sub ConsolidateRows()
Dim i As Long
i = 1
Do While Cells(i + 1, "D") <> ""
If Cells(i, "D").Value = Cells(i + 1, "D").Value Then
Cells(i, "E").Value = Cells(i, "E").Value + _
Cells(i + 1, "E").Value
Rows(i + 1).Delete
Else
i = i + 1
End If
Loop
End Sub





--
Regards,
Tom Ogilvy



"milleroy" <[email protected]> wrote in
message news:[email protected]...
>
> Hi I really need some help with this one - Please !
>
> I have a table with 5 columns.
> I want to use VB to look for a duplicate in column D, when one (or
> more) is found, add up the duplicates in column E and delete the
> unwanted rows. As in example
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 56
> Prod 3 44
> Prod 3 10
> Prod 1 33
> Prod 1 67
>
> What I would like it to show is
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 110
> Prod 1 100
>
> Any suggestions ?
>
>
> --
> milleroy
> ------------------------------------------------------------------------
> milleroy's Profile:
http://www.excelforum.com/member.php...o&userid=29870
> View this thread: http://www.excelforum.com/showthread...hreadid=511781
>

[Toppers]

Hi,
Try this:

Sub SumDuplicates()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, 4) = Cells(r - 1, 4) Then
Cells(r - 1, 5) = Cells(r - 1, 5) + Cells(r, 5)
Rows(r).Delete shift:=xlUp
End If
Next r


End Sub"milleroy" wrote:

>
> Hi I really need some help with this one - Please !
>
> I have a table with 5 columns.
> I want to use VB to look for a duplicate in column D, when one (or
> more) is found, add up the duplicates in column E and delete the
> unwanted rows. As in example
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 56
> Prod 3 44
> Prod 3 10
> Prod 1 33
> Prod 1 67
>
> What I would like it to show is
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 110
> Prod 1 100
>
> Any suggestions ?
>
>
> --
> milleroy
> ------------------------------------------------------------------------
> milleroy's Profile: http://www.excelforum.com/member.php...o&userid=29870
> View this thread: http://www.excelforum.com/showthread...hreadid=511781
>
>

[Bob Phillips]

Sub Reformat()
Dim i As Long
Dim rng As Range
Dim prev
Dim amt

For i = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
If Cells(i, "D").Value = Cells(i - 1, "D").Value Then
amt = amt + Cells(i, "E").Value
If rng Is Nothing Then
Set rng = Rows(i - 1)
Else
Set rng = Union(rng, Rows(i))
End If
Else
Cells(i, "E").Value = amt + Cells(i, "E").Value
amt = 0
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"milleroy" <[email protected]> wrote in
message news:[email protected]...
>
> Hi I really need some help with this one - Please !
>
> I have a table with 5 columns.
> I want to use VB to look for a duplicate in column D, when one (or
> more) is found, add up the duplicates in column E and delete the
> unwanted rows. As in example
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 56
> Prod 3 44
> Prod 3 10
> Prod 1 33
> Prod 1 67
>
> What I would like it to show is
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 110
> Prod 1 100
>
> Any suggestions ?
>
>
> --
> milleroy
> ------------------------------------------------------------------------
> milleroy's Profile:
http://www.excelforum.com/member.php...o&userid=29870
> View this thread: http://www.excelforum.com/showthread...hreadid=511781
>

[milleroy]

THANKS EVERYONE - IT WORKS AND SAVES ME LOADS OF TIME

Sub Reformat()
Dim i As Long
Dim rng As Range
Dim prev
Dim amt

For i = Cells(Rows.Count, "D").End(xlUp).Row To 2 Step -1
If Cells(i, "D").Value = Cells(i - 1, "D").Value Then
amt = amt + Cells(i, "E").Value
If rng Is Nothing Then
Set rng = Rows(i - 1)
Else
Set rng = Union(rng, Rows(i))
End If
Else
Cells(i, "E").Value = amt + Cells(i, "E").Value
amt = 0
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"milleroy" <[email protected]> wrote in
message news:[email protected]...
>
> Hi I really need some help with this one - Please !
>
> I have a table with 5 columns.
> I want to use VB to look for a duplicate in column D, when one (or
> more) is found, add up the duplicates in column E and delete the
> unwanted rows. As in example
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 56
> Prod 3 44
> Prod 3 10
> Prod 1 33
> Prod 1 67
>
> What I would like it to show is
>
> column D column E
> Prod 1 2
> Prod 2 3
> Prod 3 110
> Prod 1 100
>
> Any suggestions ?
>
>
> --
> milleroy
> ------------------------------------------------------------------------
> milleroy's Profile:
http://www.excelforum.com/member.php...o&userid=29870
> View this thread: http://www.excelforum.com/showthread...hreadid=511781
>