How to search for multiple substrings in a cell using UsedRange.Find(What:=, LookAt:=)

Question

I have two excel spreadsheets that list peoples names in different formats.
One sheet lists the names formatted as "First Last", organizes the names by department, and does not list their ID number. The other sheet lists the names formatted as "Last, First", organizes them alphabetically, does not show their department, and shows their ID number.

What I need to accomplish is to pull the names and ID numbers of people from specific departments. Rearranging the First and Last names to match hasn't been an issue. The issue that I am having is that sometimes the names do not match.

For example: Sheet 1 lists the name as "John Smith" Sheet 2 lists the name as "Smith Jr, John"

In this example I would search sheet 2 for "Smith, John" and yield no results.

Unfortunately these sheets are managed by other people so I cannot make edits to the sheets.

Here I split the name from sheet 1 and format it like sheet 2.

Dim nameTemp() As String = Split(CType(curSheet.Range("J" & xlRow).Value, String))
name = Trim(nameTemp(1)) + ", " + Trim(nameTemp(0))

Here I look in the second sheet for matches. This misses any names that have Jr, III, II etc. listed in the second sheet but not the first.

idRange = curSheet.UsedRange.Find(What:=name, LookAt:=XlLookAt.xlPart)

What I've been searching for is a way to search for cells that contain both the substrings:

nameTemp(0) and nameTemp(1)

Using the UsedRange.Find method if at all possible.

I've tried:

idRange = curSheet.UsedRange.Find(What:=(nameTemp(0) And nameTemp(1)), LookAt:=XlLookAt.xlPart)

And

idRange = curSheet.UsedRange.Find(What:=nameTemp(0)What:=nameTemp(1), LookAt:=XlLookAt.xlPart)

And other scenarios that I did not document.

You have to do two separate Finds in that case, you can't put two things into one "What". — SJR
So, find the ranges for the first name, then find the ranges for the second name and then perform an intersection or something similar? — TinMan464
If you want to check both are found, you just check that both ranges are Not Nothing. However, you'll have problems with inconsistent text etc. — SJR

Ricardo A Ricardo A · Accepted Answer · 2019-04-17T17:42:58

Not an answer but too long to be a comment.

Usually when I run into problems like this, I first try to make the names on both sheets follow the following format: Last, First Middle. Once I accomplish that I do a vLookup on both sheets with each other using a wildcard:

VLOOKUP(MID(FullName, 1, LEN(FullName)*.90))&"*", Range, 1, FALSE)

This trims the names into 90%, in case there are middle names/initials on one list and there isnt any on the other. So it will take Smith, John Jr, trim it to Smith, Joh* and VLookup that on the other sheet. I do this on both sheets because some may have middle names on one, some may have middle names on the other.

If only one of the Sheets has middle names, it becomes much simpler, you do the wildcard VLOOKUP from the sheet without middle names into the sheet that may have middle names.

How to search for multiple substrings in a cell using UsedRange.Find(What:=, LookAt:=)

1 Answers