Find Duplicate Values, Sum Amount, Keep Most recent Row

K

kentster

New Member
Joined
Nov 22, 2016
Messages
28
Hi - I am using Excel 2013. I have tried Consolidated Data but it just adds the dates.

I have created the "Beginning" and "End" tables below as an example. Is it possible to have a VBA code that accomplishes this? Starting with the Beginning table, find the duplicated values in Column 1. From there, add the values in Column 3 for those duplicate rows. Then find the row from those duplicate rows which has the most recent date and keep that row.

The end result is the End table. One row for each of the duplicates which has the summed value, and the most recent date (all other data would simply flow from the row selected with the most recent date). I hope that made sense.

See Below:

[TABLE="width: 491"]
<tbody>[TR]
[TD]Beginning[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Opp+Lvl6[/TD]
[TD]SaaS Opp #[/TD]
[TD]Amount[/TD]
[TD]Level 6[/TD]
[TD]Level 7[/TD]
[TD]Is it in SW Fcst[/TD]
[TD]Date Updated[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]1/3/2016[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]B[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]5/1/2016[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]C[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]10/5/2016[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]C[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]5/5/2015[/TD]
[/TR]
[TR]
[TD]D1[/TD]
[TD]D[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 491"]
<tbody>[TR]
[TD="colspan: 3"]End result after Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Opp+Lvl6[/TD]
[TD]SaaS Opp #[/TD]
[TD]Amount[/TD]
[TD]Level 6[/TD]
[TD]Level 7[/TD]
[TD]Is it in SW Fcst[/TD]
[TD]Date Updated[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]A[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]1/3/2016[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]C[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]10/5/2016[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]C[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
[TR]
[TD]D1[/TD]
[TD]D[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD]Y[/TD]
[TD="align: right"]11/1/2016[/TD]
[/TR]
</tbody>[/TABLE]


I appreciate everyone's assistance here. I will keep trying with the hopes this can be figured out.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Give this a try on a copy of your file.

Code: 
Sub t()
Dim i As Long, lr As Long, fn As Range, fAdr As String
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        If Application.CountIf(.Range("A:A"), .Cells(i, 1).Value) > 1 Then
            Set fn = .Range("A1:A" & i).Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    Do
                        fn.Offset(, 2) = fn.Offset(, 2).Value + .Cells(i, 3).Value
                        If fn.Offset(, 6).Value < .Cells(i, 7).Value Then
                            fn.Offset(, 6) = .Cells(i, 7).Value
                        End If
                        Set fn = .Range("A2:A" & i).FindNext(fn)
                    Loop While fn.Address <> fAdr
                    Rows(i).Delete
                End If
        End If
    Next
End With
End Sub
 
Upvote 0
Hi - Thanks. One little snag it appears. I tried it out on my data below, and it looks like it is adding D2+D3+D4 for the duplicate value of XXX1, instead of D2+D4+D7. The sum of the values for A1 duplicates is showing 220 with the code you had provided when it should be 210. It was correct for BBB2. I tried a sort in the code before I ran your code, but it was the same result. Below is a snapshot of how my data could look before running the macro.

[TABLE="width: 523"]
<colgroup><col span="7"><col></colgroup><tbody>[TR]
[TD]Row[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XXX1[/TD]
[TD]XXX[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]10/5/2016[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B1[/TD]
[TD]B[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]-[/TD]
[TD]5/1/2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]XXX1[/TD]
[TD]XXX[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]11/1/2016[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C3[/TD]
[TD]C[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]7/5/2016[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C4[/TD]
[TD]C[/TD]
[TD]30[/TD]
[TD]4[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]8/15/2016[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]XXX1[/TD]
[TD]XXX[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]9/3/2016[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]D2[/TD]
[TD]D[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]9/23/2016[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]F1[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]3/4/2016[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]BBB2[/TD]
[TD]BBB[/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]10/5/2016[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]BBB2[/TD]
[TD]BBB[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]10/7/2016[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]G1[/TD]
[TD]G[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]-[/TD]
[TD]11/23/2016[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]H2[/TD]
[TD]H[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]A[/TD]
[TD]-[/TD]
[TD]12/1/2016[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]I1[/TD]
[TD]I[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]-[/TD]
[TD]8/5/2016[/TD]
[/TR]
</tbody>[/TABLE]



I appreciate the assist!
 
Upvote 0
Possibly, another option:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG05Dec13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, DK [COLOR="Navy"]As[/COLOR] Variant, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, 6).Value > .Item(Dn.Value).Offset(, 6).Value [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Item(Dn.Value).Address = Dn.Address [COLOR="Navy"]Then[/COLOR]
        .Item(Dn.Value).Offset(, 2).Value = .Item(Dn.Value).Offset(, 2).Value + Dn.Offset(, 2).Value
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
EUREKA! I messed around with multiple examples whic had significant rows of data and I just modified your code to find the right columns and all worked out perfectly! Awesome stuff! Thank you so much!
 
Upvote 0
kentster said:
EUREKA! I messed around with multiple examples whic had significant rows of data and I just modified your code to find the right columns and all worked out perfectly! Awesome stuff! Thank you so much!
Click to expand...

here is what I finally arrived at.

Code: 
Sub t()
Dim i As Long, lr As Long, fn As Range, fAdr As String, dRw As Long
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = lr To 3 Step -1
        If Application.CountIf(.Range("A:A"), .Cells(i, 1).Value) > 1 Then
            Set fn = .Range("A1:A" & i).Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    Do
                        If fn.Address = fAdr Then
                            Range(fAdr).Offset(, 2) = fn.Offset(, 2).Value + .Cells(i, 3).Value
                                If fn.Offset(, 6).Value < .Cells(i, 7).Value Then
                                    Range(fAdr).Offset(, 6) = Cells(i, 7).Value
                                End If
                            Rows(i).Delete
                        Else
                            Range(fAdr).Offset(, 2) = Range(fAdr).Offset(, 2).Value + fn.Offset(, 2).Value
                                If Range(fAdr).Offset(, 6).Value < fn.Offset(, 6).Value Then
                                    Range(fAdr).Offset(, 6) = fn.Offset(, 6).Value
                                End If
                                dRw = fn.Row
                        End If
                        Set fn = .Range("A2:A" & i - 1).FindNext(fn)
                        If Not IsEmpty(dRw) Then
                            .Rows(dRw).Delete
                            dRw = Empty
                        End If
                    Loop While fn.Address <> fAdr
                End If
        End If
    Next
End With
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
597
OilEconomist
O
M
  • Solved
VBA to find most recent Invoice Date for each Customer
2
Replies
10
Views
956
kevin9999
K
R
  • Solved
VBA Question: How to find duplicate rows, and sum values after finding duplicates?
2
Replies
14
Views
2K
Alex Blakenburg
A
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
358
gtd526
gtd526
Ramballah
  • Solved
Removing duplicates and adding amounts
2
Replies
18
Views
362
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top