How to remove duplicate values from 2 columns in excel using vba

Question

I am new to Excel VBA Programming. I have one excel sheet with two columns and each column has some email adresses separated by @@. like
ColumA
[email protected]@@[email protected]@@[email protected]
[email protected]@@[email protected]

ColumnB
[email protected]@@[email protected]
[email protected]

As you can see that both column has two rows, I need 3rd column that should contain all the unique values like
ColumnC
[email protected]@@[email protected]@@[email protected]@[email protected]
[email protected]@@[email protected]@@[email protected]

Thanks

I assume you made a typo in ColumnC and there should be two @@ before [email protected], not one. — Gaijinhunter, Oct 18 '11 at 07:23

brettdj · Accepted Answer · 2011-10-18T10:10:42.740

Something like this with variant arrays and a dictionary is an efficient process of getting your desired outcome

[updated to remove delimiter at front of string, code is flexible on delimiter length] SO seems to have removed the ability to upload image so my picture has fallen off ....

Sub GetUniques()
Dim strDelim As String
Dim X
Dim Y
Dim objDic As Object
Dim lngRow As Long
Dim lngRow2 As Long
strDelim = "@@"
Set objDic = CreateObject("scripting.dictionary")
X = Range([a1], Cells(Rows.Count, "B").End(xlUp)).Value2
For lngRow = 1 To UBound(X, 1)
    X(lngRow, 1) = X(lngRow, 1) & strDelim & X(lngRow, 2)
    Y = Split(X(lngRow, 1), strDelim)
    X(lngRow, 1) = vbNullString
    For lngRow2 = 0 To UBound(Y, 1)
        If Not objDic.exists(lngRow & Y(lngRow2)) Then
            X(lngRow, 1) = X(lngRow, 1) & (strDelim & Y(lngRow2))
            objDic.Add (lngRow & Y(lngRow2)), 1
        End If
    Next lngRow2
    If Len(X(lngRow, 1)) > Len(strDelim) Then X(lngRow, 1) = Right(X(lngRow, 1), Len(X(lngRow, 1)) - Len(strDelim))
Next lngRow
[c1].Resize(UBound(X, 1), 1).Value2 = X
End Sub

If I could offer any suggestion, it would be to declare strDelim as a string. Right now it's a variant (and objDic is both a variant and object in this case as well). — Gaijinhunter, Oct 18 '11 at 09:35

Gaijinhunter · Answer 2 · 2011-10-18T09:18:32.500

Here's my take. How it works:

Dump columnA and B into a variant array
Combine each row, split into an array of emails, then weed out dupes with a dictionary.
Combine unique list into a single string and store in a new array
Transpose the new array onto column C.

Sub JoinAndUnique()

Application.ScreenUpdating = False
Dim varray As Variant, newArray As Variant
Dim i As Long, lastRow As Long
Dim temp As Variant, email As Variant
Dim newString As String, seperator As String
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

seperator = "@@"
lastRow = range("A" & Rows.count).End(xlUp).Row
varray = range("A1:B" & lastRow).Value
ReDim newArray(1 To UBound(varray, 1))

On Error Resume Next
For i = 1 To UBound(varray, 1)
    temp = Split(varray(i, 1) & seperator & varray(i, 2), seperator)
    For Each email In temp
        If Not dict.exists(email) Then
            dict.Add email, 1
            newString = newString & (seperator & email)
        End If
    Next
    newArray(i) = Mid$(newString, 3)
    dict.RemoveAll
    newString = vbNullString
Next

range("C1").Resize(UBound(newArray)).Value = Application.Transpose(newArray)
Application.ScreenUpdating = True

End Sub

Note: It's fairly similar to brettdj's answer, but there are a few differences worth mentioning:

I used more meaninful names for variables (for readability and to make it easier to edit)
I do clean up of the "@@" at the start of the sentence
I use a new array rather than overwrite the values of an existing one
I choose to clear the dictionary after each cell
I choose to use "on error resume next" and just dump entries into the dictionary instead of checking if they exist or not (personal preference, makes no major difference)

Issun, I'm puzzled as to you would post this given it is virtually indentical to my code with some cosmetic (rather than key) differences. But on the parts you did change (1) using Str and Lng etc is more usual for prefixing variable names [Hungarian Notation](http://en.wikipedia.org/wiki/Hungarian_notation) (2) This comment makse sense as an adjustment (3) The array is created only for the code so it may as well be manipulated - it doesn't "exist" as such (4) I didn't need to add the extra step (especially as its inside a loop) of cleaning the dictionary as I appended each unique row number — brettdj, Oct 18 '11 at 09:04
Please don't misunderstand - I have no ill intentions in posting a simular solution. I wanted to provide OP a solution that would be easier to understand by both adding an explanation and using diff. variable names. Hungarion Notation is a perference, one I prefer not to use as the scope of VBA code is very small and it makes it less readable IMO. No problem with you use of the dict - I am just showing a different solution (doing a concat to add the row number to each entry felt a little hard unclear to me). Again, no hard feelings, the beauty of this site is that everyone has a diff. take. — Gaijinhunter, Oct 18 '11 at 09:10
I had no hard feelings Issun and didn't intend my comment to be read that way. I thought I should step through your comments to be clear to any readers as to why I had run my code the way I did as I thought the "key" differences part implied that I had made some sub-optimal choices rather than style formatting. I fully agree that every coder has their own style and preferences. Non issue, cheers :) — brettdj, Oct 18 '11 at 09:23
Cheers :) The 'key differences' was meant to mean "characterstic differences", aka, changes worth pointing out (not better). ^^ — Gaijinhunter, Oct 18 '11 at 09:32

score 0 · Answer 3 · edited May 23 '17 at 12:12

0

The easiest way to do this would be to use the dictionary object, split function, and join function. Of course, you don't need to use those exact ones, but give it a try and see what you get.

edited May 23 '17 at 12:12

Community

1
1

answered Oct 17 '11 at 21:31

Jon49

4,444
4
36
73

How to remove duplicate values from 2 columns in excel using vba

3 Answers3