match names from two diff columns where the names are not same order 1

[cheryl27284]

I have two different columns with names from different databases. I need to see if the names in the second column are in the first column. The names are the same but in one column they are first, last and in the second column they are last first. Some names have intials and in the other column they do not. I need for the third column(with the formula) to return the name where it matches. For example I want it to look something like this: John Doe(first column)Doe N John(second column) Third column would have John Doe So I know he exist in the first column. If the first column has Sarah Smith and she is not found in the second column. The third colum would be blank.

 

[gizmo1973]

As far as I know this is going to be fairly hard to do in XL or Access (you don't say which)if the data is not an exact match, if it was you could use v-lookup.
I have to do very similar things but with comapny names and have to use specific matching software,
What would happen if you had J Smith and J Smyth would that be a match or a typo?
You could try using MS soundex for something like this I think.

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[cheryl27284]

It's excel

 

[bluedragon2]

Quick way:

1. Do a text to columns on the second column using space as delimiter.

2. You should now have three columns that look like:

Last Name / Initial or First name / First Name or Blank

3. For referance the columns are B, C and D. Now In Col E put: =IFISBLANK(D1),C1&" "&B1,D1&" "&B1)

4. Now you have a list of names that should look like Col A. Now use a lookup function to find matches.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cheryl27284]

I did it exactly like you told me and in the same columns but I'm getting an error that reads"The formula you typed contains an error etc.." It highlights the last b1.

 

[bluedragon2]

I missed a ( in the formula. It should read:

=IF(ISBLANK(D1),C1&" "&B1,D1&" "&B1)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cheryl27284]

Your awesome Blue! Thanks!

 

[KenWright]

Cheryl, notice that bit of text bottom left under Blue's posts (The bit with the purple star)

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[01Patrik]

Try this.

=IF(ISERROR((FIND(MID(A1,1,FIND(" ",A1)-1),B1))+(IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),0,IF(FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)=1,0,MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)))))+FIND(MID(A1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))+1,100),B1)),"",A1)

Place this in C1, and fill down. (Oops, I see know that it compares A1 to B1, and returns A1 if matched - not the other way around - but you get the picture, easy change if you need it)

This ignores any one-letter initial, only compares first and last names. You need to remove any comas present first, by replacing them with nothing.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.