excel vba remove duplicates

Question

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.

i've added steps to my solution, please look at it, and see if it helps, if not feel free to ask what you don't understand — Avishay Cohen
– Avishay Cohen, Commented Aug 7, 2015 at 6:56

mike · Accepted Answer · 2015-08-12 13:50:18Z

2

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)

edited Aug 12, 2015 at 13:50

user447688

answered Aug 6, 2015 at 13:06

mike

214 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michal Schmitt · Accepted Answer · 2015-08-06 13:50:12Z

0

Assuming:

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

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

answered Aug 6, 2015 at 13:50

Michal Schmitt

2162 silver badges5 bronze badges

Comments

Avishay Cohen · Accepted Answer · 2015-08-07 06:55:13Z

0

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

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)
copy column E into column E - as values
use excel's remove duplicates function
copy column E to column B

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

edited Aug 7, 2015 at 6:55

answered Aug 6, 2015 at 12:59

Avishay Cohen

2,2882 gold badges24 silver badges35 bronze badges

2 Comments

jason bourne Over a year ago

Do I not need to apply the sumifs on every line?

Avishay Cohen Over a year ago

Yes you should. After adding the formula to column E, copy it as values in place to column E, then use remove duplicate, and copy column E to column B. i'm not next to a computer now, I'll edit the answer with ordered steps when I get back home

Collectives™ on Stack Overflow

excel vba remove duplicates

3 Answers 3

Comments

Comments

those are the steps needed

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

those are the steps needed

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related