comparing Column on different sheets

0
votes

I have Sheet1 with a variable number of account numbers in column A, I want to compare them with a variable number of account numbers in Sheet2 column A.

I want to collate the two columns together on sheet3 but not duplicate the account numbers.

So basically:

Sheet1:

A1 
a3334
a4455
a76655

Sheet2:

A1
a4455
a76655
a3024
a53525

then sheet3 would end up looking like:

Sheet3:

A1
a3334
a4455
a76655
a3024
a53525

Thanks in advance.

1
vbaexcel

1 Answers

1
votes

The following should work. It combines the two columns from sheet1 and sheet2 into sheet 3, and then removes the duplicates.

Sub combineandcompare()
Dim range1 As Range, range2 As Range

Set range1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A65536").End(xlUp))
Set range2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Range("A65536").End(xlUp))

For Each x In range1
    For Each y In range2
             Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).Value = x.Value
            Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).Value = y.Value
    Next y
Next x

Worksheets("Sheet3").Columns(1).RemoveDuplicates Columns:=Array(1)

End Sub