Returning ALL matching results based upon a text-based LOOKUP VALUE

**colunga81** · 03-13-2013, 03:54 PM

Hi All --

I was brought to your website based upon a Google search, and it looks like this question has been asked a number of ways, solved in certain cases by folks actually replying in-line or by providing a spreadsheet attachment to solve the issue, so I figured that it would be best to try my hand with my unique situation.

Background:

The situation I am in is one that has two different sales teams looking to assign themselves to the same database of clients, but the database includes a large number of duplicate records, and the teams cannot overlap in their client assignments. I am attempting to take a list of TEAM A's assignments, and match it against TEAM B's assignments, but attempt to match based upon the LAST NAME of the client, and return a few values, which are:

(1) The last name that was matched
(2) The first name on the record that was matched based upon the last name field value
(3) The constituent ID that was matched based upon the last name field value

Because we cannot trust the constituent ID's accuracy (due to the duplicate records in the DB), we must attempt to "flag" records based upon naming conventions, which will then produce a list for a sales executive to go through the database in a one by one fashion inside of the DB to determine if (1) the records are unique, and (2) if not, then merge the records inside of the database system.

I need to produce a starting list for this executive to move forward.

Simply looking for constituent ID overlaps will not be enough to produce what this executive needs.

The Challenge:

As you know, VLOOKUP is not going to cut the mustard here. It will only look through the array until it finds the very first match based upon the last name field in the table array, then return the subsequent results (first name/constituent ID) that corresponds with the first match.

The Expected Outcome:

My expected outcome is to develop a script that can look at the "Last Name" field value from TEAM A's list, look at TEAM B's assignments as a lookup array (e.g. Column A-C), and if there is a match from TEAM A's "last name" against the column of "last names" in TEAM B's list -- then it would return all results (field values for Columns A-C) in-line with the matched results with the

So, having said all of that -- here's an example table that I will need to work with, and would welcome anyone's assistance in solving this issue. Please advise.

Example Data Table:

Column Key:
A = Last Name
B = First Name
C = Constituent ID

TEAM A's ASSIGNMENT TABLE
Last,First,ID
Ross,John,13
Roberts,Amanda,58
Williams,Randy,67
Williams,Bill,191
Roberts,Greg,81

TEAM B's ASSIGNMENT TABLE
Last,First,ID
Jurgenson,Sunny,1343
Johnson,Jeff,2544
Williams,Will,8912
Roberts,Gregory,9019
Ross,Wilber,45682

Assumptions on next steps:

I am assuming that since each team's total assignment size is ranging in the several thousands of rows of information, that the first best step is to follow this path prior to executing whatever help you can provide:

(1) Check for duplicate records based upon constituent ID value (this is the easy one), which will decrease the overall list size, then:
(2) Use a script to identify if the "Last Name" in each row contained in the list from TEAM A is found anywhere in the table range for TEAM B's assignment list, example script used below:

Please Login or Register  to view this content.

(In this example, I have TEAM A's values in column A-C, and TEAM B's values in column K-M, where column K = TEAM B Last Name Column)

(3) Then take whatever help this forum can provide to me at this point and then apply that to anything that has been flagged as "Investigate" from step 2.

... Is this a correct approach?

Please help me Obi-Wan Kenobi, you're my only hope!

-- GC