CODEWITHSUNDEEP
excelvba
jason bourne
jason bourne

Reputation: 97

excel vba remove duplicates

I have the below table : 

Name, Total, Email Address  Date
Test1,12,[email protected]  12/12/2012
Test2,12,[email protected]  12/05/2015
Test2,12,[email protected]  12/05/2015
Test3,12,[email protected]  12/07/2016

I want to match on Name, Email Address and Date. If an existing record is found then I want to merge them and add the totals together. e.g.

Test2,12,[email protected]  12/05/2015
Test2,12,[email protected]  12/05/2015

would become

Test2,24,[email protected]  12/05/2015

What options do I have?. If i iterate sequentially and check for every one it would take a substantial amount of time.. (was thinking to use range check and check current row against all, if found then delete and add a new row).Would appreciate some examples.

Upvotes: 0

Views: 272

Answers (3)

mike
mike

Reputation: 21

You can add a 5th column and CONCAT columns Name,Email,Date, with commas between them. E.g. Test1,[email protected],12/12/2012.

Export this column in another sheet and apply Remove duplicates so you have unique data.

Now apply a SUMIF according to this column and sum data from Total as:

SUMIF(range:"your tabel",criteria: the 5th column, sum range: column Total)

Upvotes: 2

Avishay Cohen
Avishay Cohen

Reputation: 2228

why not simply have column E sum the values like this:

=SUMIFS(B:B,A:A,"="&A2,C:C,"="&C2,D:D,"="&D2)

and when you finish copy as values and remove duplicates using the function on the ribbon? this way you can copy at the end column E to column B and be done with it in a couple of minutes edited: clarity and steps

those are the steps needed

  1. place the following formula in cell E1, and pull it down until it's in all of column E until the end of the data

    =SUMIFS(B:B,A:A,"="&A2,C:C,"="&C2,D:D,"="&D2)

  2. copy column E into column E - as values

  3. use excel's remove duplicates function

  4. copy column E to column B

and that's the full steps needed to accomplish your task.

Upvotes: 0

Michal Schmitt
Michal Schmitt

Reputation: 216

Assuming:

  1. The list is sorted by ColA
  2. Your table header is in Row3
  3. There is only 1 duplicate

  4. If ColA is the same, so are C and D

    Dim i As Integer
Dim cellCount As Integer

cellCount = Application.CountA(Range("A:A")) - 1

For i = 4 To cellCount
    If (Cells(i, "A").Value = Cells(i + 1, "A").Value) Then
        Cells(i, "B").Value = Cells(i, "B").Value + Cells(i + 1, "B").Value
    End If
Next i

ActiveSheet.Range("A:D").RemoveDuplicates Columns:=1, Header:=xlYes

Upvotes: 0

Related Questions