compare two workbooks for differences in a few columns of names

[sumhungl0]

good day everyone,
I need help to compare a few columns in one workbook to another few columns in another workbook. vba or just simple conditional formatting, doesn't matter to me. the following is what I am working with.

book1: is a name list with position number. column A has position number and column B is last name comma first name. this workbook does not belong to me and I cannot update it. names change from time to time so I need to look at it often to find changes.

book2: is my workbook and I need to make sure my list of names stays up to date with the list of names from book1. my book2 has a different format of names then book1. mine is column A position number, column B last name, column C first name.

what I am looking for is an easier way to make sure these names match at all times. is there any way I can automate this process? maybe highlight the cells/rows that don't match on my book2? I am open to suggestions if there is a better way of handling this. ideas?

any help is greatly appreciated, thanks in advance.

 

[AFPathfinder]

Since you'd be looking left of the lookup value, I'd suggest using an INDEX function. From book2, you can just concatenate the name into the format listed in book one to search by ("=B2&", "&C2"). This method will not help you ID any names in book1 that are not listed in book2, though. If you're looking to identify all discrepencies between the two books, a pair of INDEX functions in an IF statement can probably do the trick, but it would be best to use VBA.

The benefit of VBA is you can have it use colors instead of an extra column of values. It can even perform the necessary fixes for you, too (just gotta map out the logic).

 

[sumhungl0]

do you have any examples?
as for the name in book1, there are some problems from time to time. names not always following the same format. mostly "last, first mi.", sometimes the middle initial missing or no period, sometimes comma between last and first missing. I think it would be best to do a find/index/match maybe based on my book2 searching for last name matching and then another searching for first name. what do you think? maybe I could insert a few columns to contain these formulas.
still open to vba as well, however im not very experienced in that aspect. again some examples would be great.

thanks for the response.

 

[AFPathfinder]

I guess it falls to how much work you'll have to do after the formula. VBA can help alleviate some of those differences if you stick to a couple conventions, but I don't think it will catch all possibilities without implementing a string comparison percent function. If a person's name is Donald in your book, but listed as Allen in book1, there's just going to be some that you can't catch.

I think the ideal solution would be to implement controls to standardize the naming conventions in book1, but that's probably biting off more than you would care to chew.

As far as conventions, I would suggest removing all the hyphens in your book and the VBA can remove the hyphens for comparison in book1. Instead of using a comma as a separator, we can use the first space as the definitive separation from last and first. I'd suggest disregarding middle initials, suffixes, and prefixes; just sticking with the first and last name. Is it possible that book 1 might have the name listed as "first last"?

This example does a string search for the first and last name separately on a string value and places a comment in column D for each row. Make sure to test this out before saving any changes as I haven't tested this one.

Sub NameMatching()
    Dim i As Integer
    Dim j As Integer
    Dim m As Integer
    Dim n As Integer
    Dim finalRowMaster As Integer
    Dim finalRowOther As Integer
    Dim fName As String
    Dim lName As String
    
    Dim oMasterWb As Worksheet
    Dim oOtherWb As Worksheet
    
    Set oMasterWb = ThisWorkbook.Sheets(1)
    Set oOtherWb = Workbooks("book1.xlsx").Sheets(1)
    
    oMasterWb.Activate
    finalRowMaster = oMasterWb.Cells(Rows.Count, 1).End(xlUp).Row
    finalRowOther = oOtherWb.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To finalRowMaster
        fName = Cells(i, 3).Value
        lName = Cells(i, 2).Value
        
        For j = 2 To finalRowOther
            If InStr(oOtherWb.Cells(j, 2).Value, fName) > 0 And _
                InStr(oOtherWb.Cells(j, 2).Value, lName) > 0 Then
                Cells(i, 4).Value = "Match Found"
                m = m + 1
                Exit For
            End If
        Next j
        
        If Cells(i, 4).Value = "" Then
            Cells(i, 4).Value = "Research Needed"
            n = n + 1
        End If
    Next i
    
    MsgBox "Macro has finished." & vbNewLine & vbNewLine & m _
        & " matches were found." & vbNewLine & n & " names need researched."
        
    Set oMasterWb = Nothing
    Set oOtherWb = Nothing
End Sub

 

[István Hirsch]

Try this. Now Sheet 1 represents Book 1, Sheet 2 represents Book 2. Results are in Sheet 1, columns C and D:

Excel Workbook
	A	B	C	D
1	Pos	Recent list	Not in the recent list	Not in your old list
2		Hemingway, Ernest mi
3		Zola, Emile	Fellowes, Julian
4		Tompa, Andrea		Tompa, Andrea
5		Heller, Joseph		Heller, Joseph
6		Highsmith Patricia mi		Highsmith Patricia mi
7		Tartt Donna
Sheet1

Excel Workbook
	A	B	C
1	Pos	Your old	list
2		Hemingway	Ernest
3		Fellowes	Julian
4		Zola	Emile
5		Tartt	Donna
Sheet2