Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicates | Excel Worksheet Functions | |||
Condensing a list with duplicates to a list with non-duplicates | Excel Worksheet Functions | |||
Duplicates in excel that aren't 100% DUPLICATES ... | Excel Discussion (Misc queries) | |||
Duplicates | Excel Worksheet Functions | |||
Duplicates | Excel Discussion (Misc queries) |