Comparing 2 text columns in Excel 2003, return non-matches

[budson]

I am looking for a formula or method to compare 2 columns of names in a single excel worksheet (format is 'lastname, firstname') where the results returned to a seperate column (C) is a list of the names that had no match in the other column. I can get True/False returns using ISNA but need to find a way to return the actual names.

If I could also return a seperate list of the names that did match that would be even better. I just need to differentiate between those that matched and those that didn't. There may be unmatched names resulting from both columns.

Thank you.
Deb

 

[Joe4]

Welcome to the Board!

Do you have Microsoft Access? What you want to do is possible in Excel, but it is really very easy in Access. Access actually has wizards to set up Matched Queries and Unmatched queries that walk you through the whole process.

 

[HalfAce]

Hello budson, welcome to the board.
If this is going to be done in excel as opposed to access then perhaps something
like this will get you started. (Assumes you have headers in row 1.)

Sub Demo()

Dim Rng As Range, List As Object, i As Long
Set List = CreateObject("Scripting.Dictionary")

[C1] = "No Match"
[D1] = "Matched"
For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If Not List.Exists(Rng.Value) Then List.Add Rng.Value, Nothing
Next

For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
  If Not List.Exists(Cells(i, "B").Value) Then
    Cells(Rows.Count, "C").End(xlUp)(2).Value = Cells(i, "B").Value
  Else
    Cells(Rows.Count, "D").End(xlUp)(2) = Cells(i, "B").Value
  End If
Next

Set List = Nothing

End Sub

 

[budson]

I do have MS Access on my system but am even more challenged with that application. I was hoping for an easy formula or such.

I can get the information into access tables but it's the query I have trouble with. I should post this to the MS Access page here?

Deb

 

[Joe4]

Deb,

That's the beautiful thing, Access's wizards will build the query for you!

Simply go to the Queries section, click on "New" and select "Find Duplicates Query Wizard" or "Find Unmatched Query Wizard", and the wizard will walk you through it.

You really don't need to know anything about Access. The hardest part is getting the data in, and it sounds like you already figured that part out.