Summarizing duplicate amounts and deleting the duplicated rows

[rockdrigotoca]

Hello everyone!
I need your help creating a macro that will help me summarize the amounts on each row, only when Name + Account coincide and then delete all the duplicated rows... can anyone help me please???

On the following example:

ColA ColB ColC Col D ColE
1Name Account Number Debt Address 1 Address 2
2Rodrigo 1234 £100.00 street 1 street 2
3John 2345 £150.00 street 3 street 4
4Smith 3456 £110.00 street 5 street 11
5James 4567 £120.00 street 6 street 12
6Alfred 5678 £130.00 street 7 street 13
7Rodrigo 1234 £100.00 street 1 street 2
8Rodrigo 1234 £100.00 street 1 street 2
9John 8888 £150.00 street 14 street 15


I need to create a new worksheet called Consolidated, that will have the overall debt of each person. If possible ,the macro needs to look at both Column A and Column B and review if there are any duplicates to add them up. In this case, Add rows 2+7+8 and paste only the whole first row into new worksheet, then row 3, then 4, etc., (in this case, row 9, John's account number is different so this should be pasted in a separate row in the new worksheet...) and the end result would look something like:

ColA ColB
1Name Account Number Overall Debt Address 1 Address 2
2Rodrigo 1234 £300.00 street 1 street 2
3John 2345 £150.00 street 3 street 4
4Smith 3456 £110.00 street 5 street 11
5James 4567 £120.00 street 6 street 12
6Alfred 5678 £130.00 street 7 street 13
7John 8888 £150.00 Street 14 Street 15

I know just a little of coding, can understand the logic behind it but don't know how to build something like this...

Can anyone help, please???

Kind regards!

Rod

 

[AlphaFrog]

Sub Consolidate()

    Dim wsNew As Worksheet, wsSource As Worksheet, i As Integer
    
    Set wsSource = ActiveSheet
    Set wsNew = Worksheets.Add(After:=wsSource)
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
        wsNew.Name = "Consolidated"
        If wsNew.Name <> "Consolidated" Then
            i = 1
            Do
                i = i + 1
                wsNew.Name = "Consolidated (" & i & ")"
            Loop Until wsNew.Name = "Consolidated (" & i & ")"
        End If
    On Error GoTo 0
    
    With wsSource
        lastrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("B1:B" & lastrow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        .Range("A1:E" & lastrow).SpecialCells(xlCellTypeVisible).Copy Destination:=wsNew.Range("A1")
        If .FilterMode Then .ShowAllData
    End With
    
    With wsNew
        .Range("C2").Formula = "=SUMIF('" & wsSource.Name & "'!B:B, B2, '" & wsSource.Name & "'!C:C)"
        lastrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("C2").AutoFill Destination:=.Range("C2:C" & lastrow)
        .Range("C2:C" & lastrow).Value = .Range("C2:C" & lastrow).Value
        For i = 1 To 5
            .Columns(i).ColumnWidth = wsSource.Columns(i).ColumnWidth
        Next i
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[rockdrigotoca]

Hi AlphaFrog!

Simply wicked!!!!!!!!!!

Thank you SO very much!!!

Cheers!

 

[wildwally]

How can i make this SUMIF a SUMIFS and look at two columns of data?

 

[rockdrigotoca]

Hi Wildwally

It would be easy, just put a + between the two SUMIFs =

=SUMIF(A1:B20,"condition",B1:B20)+SUMIF(C1:C20,"other condition",D1:D20)

If you are placing this formula in code, instead of one ", put two as in ""condition""

 

[theta]

Hi

I pretty sure I know how this works. Is there any chance you could annotate this script to aid learning process?

I am guessing the top bit tries to create 'Consolidated' but if it already exists, creates 'Consolidated (2)' etc...and this method of looping helps to identify which consolidated sheet is to be used, i.e. the latest one...so that the script can be re-run frequently?

 

[AlphaFrog]

@Theta and @rockdrigotoca,

Just to let you know that wildwally PMed me and his follow up question has been answered. He did use SUMIFS and just had a minor syntax problem.

@Theta
Yes, the top bit creates a new numbered "Consolidated" sheet e.g.; Consolidated(1), Consolidated(2), Consolidated(3) ...etc