Sum Values when Duplicate ID Exists
 

I need to create a macro to search down Column A and if their is for
example 3 duplicates to add there 3 values (from column d) together.

For example if it searched the below piece of data, it would see 1250
is listed 3 times, so it would move over to column d and sum 417+42+250
and place the total in column E on the last row of the duplicates.

COL-A ___ COL-B__ COL-C ______________ COL-D __ COL-E
1239 ____ 14 __ COMPUTER HARDWARE ______ 0 ______
1250 ____ 10 __ COURSES - TRAINING ______ 417 ______
1250 ____ 12 __ COURSES - TRAINING ______ 42 ______
1250 ____ 14 __ COURSES - TRAINING ______ 250 __ 709
1271 ____ 10 __ CUST. DUTY & NON-RECOV __ 8333 __

I have also attached a tab delimited version in .txt format.


+-------------------------------------------------------------------+
|Filename: budgets.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5109 |
+-------------------------------------------------------------------+

--
sealanes
------------------------------------------------------------------------
sealanes's Profile: http://www.excelforum.com/member.php...o&userid=36834
View this thread: http://www.excelforum.com/showthread...hreadid=565452

Sum Values when Duplicate ID Exists
 

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If Cells(i, "A").Value = Cells(i - 1, "A").Value Then
If Cells(i, "A").Value < Cells(i + 1, "A").Value Then
Cells(i, "E").Value = Application.SumIf( _
Columns(1), Cells(i, "A").Value, Columns(4))
End If
End If
Next i

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sealanes" wrote in
message ...

I need to create a macro to search down Column A and if their is for
example 3 duplicates to add there 3 values (from column d) together.

For example if it searched the below piece of data, it would see 1250
is listed 3 times, so it would move over to column d and sum 417+42+250
and place the total in column E on the last row of the duplicates.

COL-A ___ COL-B__ COL-C ______________ COL-D __ COL-E
1239 ____ 14 __ COMPUTER HARDWARE ______ 0 ______
1250 ____ 10 __ COURSES - TRAINING ______ 417 ______
1250 ____ 12 __ COURSES - TRAINING ______ 42 ______
1250 ____ 14 __ COURSES - TRAINING ______ 250 __ 709
1271 ____ 10 __ CUST. DUTY & NON-RECOV __ 8333 __

I have also attached a tab delimited version in .txt format.


+-------------------------------------------------------------------+
|Filename: budgets.txt |
|Download: http://www.excelforum.com/attachment.php?postid=5109 |
+-------------------------------------------------------------------+

--
sealanes
------------------------------------------------------------------------
sealanes's Profile:
http://www.excelforum.com/member.php...o&userid=36834
View this thread: http://www.excelforum.com/showthread...hreadid=565452

Sum Values when Duplicate ID Exists
 

Thanks for that mate, worked first time.


--
sealanes
------------------------------------------------------------------------
sealanes's Profile: http://www.excelforum.com/member.php...o&userid=36834
View this thread: http://www.excelforum.com/showthread...hreadid=565452