Compare two columns and remove mismatches

This is just a simple example so I can understand the method - Column A has more data than Column B. I need to figure out how in Column C I can have an "x" appear whenever there is a Column A match to Column B. Basically I need to remove everything from Column A that is not represented in Column B without doing it manually.
Who is Participating?
Martin LissConnect With a Mentor Older than dirtCommented:
Here's a macro that does the deletion as well. It assumes there are no headings.

Sub RemoveUnmatched()
Dim lngLastRow As Long
Dim lngRow As Long
Dim Found As Range

Application.ScreenUpdating = False

lngLastRow = Range("A65536").End(xlUp).Row


For lngRow = lngLastRow To 1 Step -1
    Set Found = Selection.Find(What:=Cells(lngRow, 1).Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
    If Found Is Nothing Then
        Cells(lngRow, 1).Delete Shift:=xlUp
    End If

Application.ScreenUpdating = True

End Sub

Open in new window

Martin LissOlder than dirtCommented:
If you don't want to delete automatically, change line 17 to

Cells(lngRow, 3).Value = "X"
Saqib Husain, SyedEngineerCommented:
Enter this formula in C2 and copy it down

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Martin LissOlder than dirtCommented:
Did any of the above help you?
K_DeutschAuthor Commented:
Like a charm...thanks.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.