Easiest Way to Find and Remove Duplicates?

Question

I have two columns filled with data in an Excel spreadsheet:

Column 1:
A
B
C
D

Column 2:
C
D
E
F
G

Desired Data: A,B,C,D,E,F,G (not A,B,C,D,C,D,E,F,G)

I want to combine the two columns, and remove any duplicates. I'm coming from a C# word, so my understanding of VBA is limited. I know I could create an array with the dimension of dimension(column1)+dimension(column2), then do some duplicate removal (is there one built in?), or something like that.

Any guidance would be appreciated, thanks!

http://stackoverflow.com/questions/5890257/populate-unique-values-in-to-vba-array-from-excel/5891317#5891317 — Tim Williams, May 11 '11 at 16:50

score 2 · Accepted Answer · answered May 11 '11 at 13:55

Use the following to do the processing...and just call it as you would any other function or subroutine.

Sub RemoveDupes()

    'Add extra Column, "A" becomes "B"
    Columns(1).EntireColumn.Insert

    'Filter out duplicates and copy unique list to "A"
    Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=Range("A1"), Unique:=True

    'Add extra Column, "B" becomes "A"
    Columns(2).EntireColumn.Delete

End Sub

Then just join and sort the results from each column :)

If you remove the dupes in each then join, you would have to remove dupes again. or you could just remove dupes once after joining them. just sayin' :) — sooprise, May 11 '11 at 13:59

Easiest Way to Find and Remove Duplicates?

1 Answers1