asked on

Excel - list cell contents that are not duplicated

Column A contains a complete list of postcodes
Column B contains a list of some of the postcodes that are listed in Column A

Column D is an example of part of the required list. The requirement is to list all postcodes that are not duplicated (listed in Column A and B)
ee-duplicate-example.xlsx

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

My code lists all codes that are in "A" but not in "B". Do you also want those that are in column "B" but not in "A"? If so then use this code instead.

Sub FindMissing()
Dim lngLastRow As Long
Dim lngRow As Long
Dim rngFound As Range
Dim lngNewRow As Long

lngLastRow = Range("A1048576").End(xlUp).Row
lngNewRow = 1
For lngRow = 2 To lngLastRow
    Set rngFound = Columns(2).Find(Cells(lngRow, "A"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
    If rngFound Is Nothing Then
        lngNewRow = lngNewRow + 1
        Cells(lngNewRow, "D") = Cells(lngRow, "A")
    End If
Next

lngLastRow = Range("B1048576").End(xlUp).Row
For lngRow = 2 To lngLastRow
    Set rngFound = Columns(1).Find(Cells(lngRow, "B"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
    If rngFound Is Nothing Then
        lngNewRow = lngNewRow + 1
        Cells(lngNewRow, "D") = Cells(lngRow, "B")
    End If
Next

End Sub

Open in new window

gregfthompson

ASKER

Thanks heaps.

Great bit of work.

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015