Compare columns in two worksheets for similar text

Venteux · #1 06-11-2022, 01:24 PM

Hi all,

I'm trying to figure out how to compare a column of names in one worksheet to a column of names in a second worksheet in the same workbook. I'm using the LIKE operator but it doesn't do what I'm looking for.

Column one will have names as Last Name, First Name
Column two has names as First Name Last Name

I'd like to find out if a name in sheet 1 doesn't appear in sheet 2 and vice versa. I don't know if this is even possible because obviously two different people might have the same first name but different last name so I don't want that name to be skipped.

I've attached a sample of my workbook for reference.

I'm obviously new at excel vba so any help is appreciated!

Code:


Sub Test2()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Name1 As String
Dim Name2 As String
Dim n As Long

Set ws1 = Sheets("Form4A")
Set ws2 = Sheets("Form4B")

For n = 3 To 250
Name2 = ws1.Range("A" & n).Value
Name1 = ws2.Range("B" & n).Value

If Name2 Like Name1 Then

Else

ws2.Cells(Rows.Count, "E").End(xlUp).Offset(1).Value = Name1 & " " & "or" & Name2 & " " & "does not appear in both Form 4A and 4B."

End If

Next n

End Sub

Logit · #2 06-12-2022, 09:26 AM

The following assumes your posted macro works successfully.
:
On the Second Sheet in a BLANK COLUMN, enter the following formula on ROW #3 :

Code:

=RIGHT(B3,LEN(B3)-SEARCH(" ",B3))&", "&LEFT(B3,SEARCH(" ",B3)-1)

Now drag that formula down the column as far as there is data in Col B.

Next, edit your macro to compare against Sheet 2, Col B.

Venteux · #3 06-13-2022, 07:03 AM

I had to tweak it a bit, but it works. Thank you!

Logit · #4 06-14-2022, 08:03 PM

Glad you have an answer. Cheers !

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to compare two csv files mainly two columns containing time	rupeshforu3	Excel	2	07-11-2017 09:03 PM
How to compare two columns and match data	Neydo222	Excel	2	09-12-2015 02:49 AM
Need help with a VLOOKUP formula & two similar, yet non-identical columns of data.	mikey386	Excel	1	12-18-2014 01:59 AM
Need To Compare Two Worksheets, Update Current Records And Insert New	David92595	Excel	1	07-20-2012 07:00 AM
How to compare 2 columns with other two columns in EXECL 2007?	Learner7	Excel	5	06-12-2010 09:54 AM