Compare partial data in two columns Then copy number value into cell when match found

[rebel004]

Hi all,

Been struggling with this formula for way too long. . I have one worksheet (VISITS) with names, birthdates and other data fields and a second worksheet (HEADERS) with the same name and birthdates. I need to match the name and birthdate columns on both sheets and when there is a match, copy the employee number from HEADERS onto a column in the VISITS sheet.

Now here's the hitch: the names in the HEADER sheet have about two or three additional characters at the end of the cell than the names on the VISIT sheet so most of the names will not be an exact match between the sheets. Example is SMITH, JAMES E in the header table and SMITH, JAME in the visit table. Also, the dates are in yyyymmdd format which may be causing me problems.

Comparing the last names and birthdays will match the correct records. Once that match is found, copy the customer number from Header table into the visit table. I have attached a sample of the file with the Header and Visit data.

Any help would be greatly appreciated!!

Rebel

[MarvinP]

Hi rebel004 and welcome to the forum,

The customer name data on the Visits sheet has a pesky char(160), which shows as a space in each name. You need to remove these for a start before you try to compare names between the two sheets. I'd suggest doing a helper column of E on the Visits sheet with the following formula.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Trim will make all double spaces a singe space and remove spaces in front and in back of text strings. Clean will make all Char(32) and lower a space. Note that Clean() doesn't work with pesky char(160) which is why the formula substitutes it with a space.

This is should get you on your way to figuring out a method using names and birthdays as suggested in your post.

I would need to write some vba code to compare the name in the Visits table with the first (length of visits name) characters in the Headers Table. I think you may be able to get an answer without needing VBA.

Let me know if the above gets you to an answer, if not I'll proceed with some code.

[rebel004]

Hello MarvinP,

Thanks so much for your reply! I can use that function you setup to "sanitize" the names in the visit table as a starting point. I'm assuming that pesky character came from converting the source data into excel: a standard reporting app thats running on a unix system.

Once the Visit names are cleaned, will it be possible to execute that formula I came up with (column E of Visit table) or would VB script work more effectively?? I haven't done any coding with VBA up to this point as I'm in the learning stages....

Thanks again..

[MarvinP]

Hi rebel004,

You should first try to do your original formula, without using and VBA. The jump from Excel formulas to VBA is a little steep. I'm hoping you can create a formula that looks at last name and birthdate to lookup your needed values.

[rebel004]

Hey MarvinP,

Okay, I will perform the clean on the visit names and run my original formula to see if they are matching.

Will repost and let you know the outcome...

Thanks

[rebel004]

HI MarvinP,

Ran the clean function on the names and used my matching formula to match as many records as possible. A lot of names were matched up, however there are more that do not match for some reason. Here the the formula I am using to attempt to match on last name and birthday:

IFERROR(INDEX(Headers!$A$3:$A$19075,MATCH(J1197&K1197,Headers!$B$3:$B$19075&Headers!$C$3:$C$19075,0),1),"NOT")

I have checked the Headers table to verify some of the nonmatching names and they appear to be exactly the same as the Visit table.

Quite confused with this one..... Can you see anything wrong with the formula and why they won't match?

[martindwilson]

change the match part to
MATCH(J1197&K1197,index(Headers!$B$3:$B$19075&Headers!$C$3:$C$19075,0),0)

[rebel004]

Trying the code now, will let you know how that works....

[rebel004]

Martindwilson,

Thanks, that new formula works great!! Now, the second half of my dilemma is the trying to match up names that are not exact. For example, in the Visit table, I have the name MESSNER, BETTY but in the Header table, the name appears as MESSNER, BETTY J. The birthdates are the same in both tables.

I have been playing with the below formula trying to compare the last name (up to the comma) in the Header and Visit table along with the birthdates to match the remaining records.

{=IFERROR(INDEX(Headers!$A$1:$A$19075,MATCH(LEFT(A2,SEARCH(",",A2,1)-2),Headers!LEFT(B2,SEARCH(",",B2,1)-2)&Headers!$C$1:$C$19075,0),1),"NOT")}

I cannot get the formula to run. Always tells me formula contains an error. Any help would be greatly appreciated...

[martindwilson]

post a sample its easier to see,i'd try to avoid arrays where possible also indicate what results you want.

[MarvinP]

Hey rebel004 and martin,

When rebel said he could match using partial names and dates I was thinking the only problem was pesky characters. I almost wrote a User Defined Function to match using names only. Now I think a formula using Find is in order. If MESSNER, BETTY is in A1 and MESSNER, BETTY J is in B1 then a If Find A1 in B1 seems cleaner without needing a birthdate. I'll noodle on this a while but have been out of the loop.

did that make sense?

[rebel004]

If a match is found between the Header and Visit name column, copy the customer number from the Header table into the Visit table.

I have the formula in the cust nbr column under the visit table.

Thanks

[MarvinP]

Did you figure it out? Did you want to give us a sample and it not attach?

Edit this thread to solved it you have it now, otherwise (I agree with martin) and give us a sample of where you are now.

[rebel004]

Trying to get my file down to 1MB in size for the sample. In the meantime, here is a snippet:

Visit Table
Customer Name Birthday
ARTHUR, JUDITH MARL 19460830
ARTHUR, JUDITH MARL 19460830
ARTHUR, JUDITH MARL 19460830
ARTHUR, JUDITH MARL 19460830
ARTHUR, JUDITH MARL 19460830
ARTHUR, JUDITH MARL 19460830

Header Table

Cusnbr Customer Name Birthday
6467 ARTHUR, JUDITH MARLENE 19460830


Match the Visit cust last name and birthdate to the Header last name and birthdate. IF the match is found, copy the cust nbr to the Visit table

ARTHUR, JUDITH MARL 6467 19460830

[rebel004]

Hey Marvin,

That logic does make sense. I was only using birthdates to try and make the names unique enough to match. If and IF Find is in order, that works for me. Not sure where to begin with that....

[martindwilson]

since the customer number is a number sumproduct or maybe sumifs would probably work

[rebel004]

I haven't written any formulas using those functions before. Can someone start me off with some sample code?

[martindwilson]

post small sample then with a few variations

[rebel004]

The attached is a sample of Visits and Header records where the names do not exactly match. The Header table is the master list which contains the full customer name and cust number. The Visit table is missing between 2 or 3 characters of the name.

I have included a few names for comparison in the visit table. Let me know if more are needed.

Thanks for all your help!

[martindwilson]

still not sure but if surname+dob is unique rather that over complicate just add 2 helpers

[rebel004]

That works great!!!! You are right, no need to complicate the situation.

Many thanks!!!!!!