VBA: removing duplicates in two columns and summing their values

[salman94]

Hi,

I have data in two tables on one sheet. I would like to remove duplicates and consolidate/sum the values for each table (separately). If possible, can you also please describe what each line does.

A B C D E
XX $$ XX $$
XX $$ XX $$



Thanks in advance.

 

[igold]

Hi,

I have some questions:

Do you want to remove duplicate rows that appear in each table or do you want to remove rows that are duplicates in both tables. So if a row is not duplicated in its own table but it is a duplicate in the other table does it get removed.

What are your table names.

 

[salman94]

Hi,

I have some questions:

Do you want to remove duplicate rows that appear in each table or do you want to remove rows that are duplicates in both tables. So if a row is not duplicated in its own table but it is a duplicate in the other table does it get removed.

What are your table names.

Hi,

I'd like to remove duplicates that appear in each table, not the latter. So if a row is a duplicate in the second table, it shouldn't get removed. Also they are not actually tables, they are just ranges (Column A and B) and (Column D and E) with their own data.

 

[igold]

Post removed for editing

 

[igold]

Ok, I was not quick enough...

See if this helps you. The code assumes you have a header row in Row 1. I hope you understand my comments...

Sub SumNoDupe()


    'Declare the variables
    Dim rng As Range
    Dim lRow As Long
    
    'Turn off screen updating for speed and to avoid flicker
    Application.ScreenUpdating = False
    
    'Determine what last row with data in column 1 or A
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Create the range declared as rng using columns A & B
    'and the last row of data found from above
    Set rng = Range("A1:B" & lRow)
    
    'Remove the duplicate from the range
    rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    
    'With duplicates now removed, determine what the last row of data is now and add 1 row
    lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    'Insert the worksheet function Sum() in column B, 1 row below the last row of data
    Cells(lRow, 2).Value = WorksheetFunction.Sum(Range("B2", "B" & lRow))
    
    'Clear out the Range (probably not needed)
    Set rng = Nothing
    
    'Go through the same steps above except using Columns D & E
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    Set rng = Range("D1:E" & lRow)
    rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    lRow = Cells(Rows.Count, 4).End(xlUp).Row + 1
    Cells(lRow, 5).Value = WorksheetFunction.Sum(Range("E2", "E" & lRow))
    
    'Turn screen updating back on
    Application.ScreenUpdating = True
    
End Sub

I hope this helps.

 

[salman94]

Sorry if I was unclear in my instructions. I meant to sum the values that were previously duplicates into one. For example: the names are in column A (A, B, C, B, A, C) with duplicates. Their corresponding $ values are in column B (A$, B$, C$, B$, A$, C$). I would like to remove the duplicates in column A and sum their corresponding $ values into one.

I believe I could use the sumif function as well.