|
vb SUM OF DUPLICATES
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 (o more) is found, add up the duplicates in column E and delete th 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 -- millero ----------------------------------------------------------------------- milleroy's Profile: http://www.excelforum.com/member.php...fo&userid=2987 View this thread: http://www.excelforum.com/showthread.php?threadid=51178 |
vb SUM OF DUPLICATES
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" wrote in message ... 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 |
vb SUM OF DUPLICATES
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 |
vb SUM OF DUPLICATES
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" wrote in message ... 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 |
vb SUM OF DUPLICATES
THANKS EVERYONE - IT WORKS AND SAVES ME LOADS OF TIME Bob Phillips Wrote: 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" wrote in message ... 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 -- millero ----------------------------------------------------------------------- milleroy's Profile: http://www.excelforum.com/member.php...fo&userid=2987 View this thread: http://www.excelforum.com/showthread.php?threadid=51178 |
| All times are GMT +1. The time now is 12:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com