MATCH 2 column with diffrent name format

santoshsapre · Mar 17, 2012

I have hug data of name in column A with first name, middile name, surname format and in column B same name but diffrent format like surname , first name and then middle name

i want any formula or macro or to highlite both cell with match name.

current have tried =exact formula but many such cases showing false

example given below :

[pre]

Code:

column A                column B             Result using =exact
prakash joshi           prakash joshi         true
prakash joshi           joshi prakash         false ( required true )
praksh joshi prakash    ramesh joshi          false ( required true )
joshi prakash prakash   joshi                 alse ( required true )
santosh sapre	        santosh sapre	      TRUE
sapre santosh	        santosh R Sapre	      TRUE
santosh rajaram sapre	sapre santosh	       TRUE
sapre santosh rajaram	santosh rajaram sapre	TRUE
santosh R sapre	        sapre santosh rajaram	TRUE
vijay patil	        shankar jadhav	       FALSE
sandip pradhan	        sandip rathod	       FALSE
Mayuresh Ramesh Pandit	Pandit Mayuresh	       TRUE

[/pre]
attached sample file

http://dl.dropbox.com/u/66400357/Book1.xlsx

DonMinter · Mar 17, 2012

Are there always three names or fewer?

=LEFT(A1,LEN(A1)-FIND(" ",A1)) That will find all the text before a space.

=RIGHT(A1,LEN(A1)-FIND(" ",A1)) That will find all the text after a space.

The problem is that you could have two spaces, sometimes. If you play with those formulas, I think you might be able to adjust them slightly to come up with what you need.

santoshsapre · Mar 17, 2012

Thanks for your promt reply ...

not clear above formula, could u pls add in my xls attached and send to me. there will be 2 or 3 names in one cell .

Cell A3 should check any 2 name with B3 and if match any 2 name then result should be as TRUE requied in c3.

Pls help

Faseeh · Mar 17, 2012

Hi santoshspare,

Welcome to the forum. This workout file is a partial solution. Someone else might come up with single formula solution to the problem. One pre-condition to this is that if you have go first second and third name for a person then they must appear in any order in the second column, if they are less then that this formula will show error.

http://www.4shared.com/file/rYK6Ae4Z/Name_Solution_my_workout.html

..will try to find a better solution for that

Regards,

Faseeh

oldchippy · Mar 17, 2012

Hi,

I've had to split it into additional columns, but not getting any errors

http://speedy.sh/yqctz/CheckNames.xlsx

santoshsapre · Mar 18, 2012

Thanks for the solution, but can u pls try to compact formula as i have to check almost more than 9,000 names on a daily basis and as per above formula will have to split both the column and then apply formula in 3 diff column to get the result. i think this will very time consume process.

Pls guide.

DonMinter · Mar 18, 2012

Santhosh,

Going back to my initial response:

Will you EVER have more than 3 names?

You can then use my first formula to find the "first" name. You can then use my second formula to find the "last" name. You would need to also find the "middle" name start and finish points using

=FIND(" ", CellWithName)

Then, a second =FIND() to find the end of the "middle" name, starting from the first find position +1, also looking for a space.

Then, you use Oldchippy's logic for the three names to do your truth table.

If you will split this up into many different columns, you can get each step to work, then you can combine the formulas in each column (or hide the columns, which is easier) and from that get your answer.

I hope this explanation helps you find your answer. If something is not clear, please let me know, and I'll try to give you an example.

Don

DonMinter · Mar 18, 2012

I noticed my =left() formula is incorrect.

The "first" name:

=LEFT(A3,FIND(" ",A3))

The "second" name:

=RIGHT(A3,LEN(A3)-FIND(" ",A3))

IF the "second" name has a space in it:

=IFERROR(FIND(" ",G3),0)

The above will give you zero if there is no space in the "second" name. It will give you the position of the space if there IS a space.

Then split the "second" name as done for the original text if the above iferror statement is NOT zero.

Then do the comparisons as oldchippy showed.

Once you get this working in separate cells, combine the formula or hide the columns, at your discretion.

Don

oldchippy · Mar 18, 2012

Hi,

I just hope it's no more than three names! Here's a formula that does work - its the longest I've ever done before, but I'm sure some Guru could shorted it to about 20 characters probably, try this

=IF(IF(OR(LEFT(A3,FIND(" ",A3)-1)=LEFT(B3,FIND(" ",B3)-1),LEFT(A3,FIND(" ",A3)-1)=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)=0,RIGHT(B3,LEN(B3)-FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)-FIND(" ",B3)-1)),LEFT(A3,FIND(" ",A3)-1)=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")="","",RIGHT(B3,LEN(B3)-IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")))),1,0)+IF(OR(IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)=0,RIGHT(A3,LEN(A3)-FIND(" ",A3)),MID(A3,FIND(" ",A3)+1,IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)-FIND(" ",A3)-1))=LEFT(B3,FIND(" ",B3)-1),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)=0,RIGHT(A3,LEN(A3)-FIND(" ",A3)),MID(A3,FIND(" ",A3)+1,IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)-FIND(" ",A3)-1))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)=0,RIGHT(B3,LEN(B3)-FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)-FIND(" ",B3)-1)),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)=0,RIGHT(A3,LEN(A3)-FIND(" ",A3)),MID(A3,FIND(" ",A3)+1,IFERROR(FIND(" ",A3,FIND(" ",A3)+1),0)-FIND(" ",A3)-1))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")="","",RIGHT(B3,LEN(B3)-IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")))),1,0)+IF(IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))="",0,IF(OR(IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))=LEFT(B3,FIND(" ",B3)-1),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)=0,RIGHT(B3,LEN(B3)-FIND(" ",B3)),MID(B3,FIND(" ",B3)+1,IFERROR(FIND(" ",B3,FIND(" ",B3)+1),0)-FIND(" ",B3)-1)),IF(IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")="","",RIGHT(A3,LEN(A3)-IFERROR(FIND(" ",A3,FIND(" ",A3)+1),"")))=IF(IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")="","",RIGHT(B3,LEN(B3)-IFERROR(FIND(" ",B3,FIND(" ",B3)+1),"")))),1,0))>=2,TRUE,FALSE)

Montrey · Mar 18, 2012

hahaha

runway · Mar 18, 2012

That man requires a large beer. NOW

oldchippy · Mar 18, 2012

@runway

I'm ready......cheers

DonMinter · Mar 19, 2012

Hats off to you, OC!

Sorry that I'm not going to test it myself. Personally, that's a candidate for "helper columns" that are hidden, if ever I have seen one.

NARAYANK991 · Mar 19, 2012

Hi oldchippy ,

I'd like to submit the following 'slightly' smaller formula :

=IFERROR(IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=0,A3=B3,IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=1,AND(SEARCH(LEFT(A3,FIND(" ",A3)-1),B3)>0,SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3)-1),B3)>0),IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=0,SEARCH(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1),A3)>0,IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,AND(SEARCH(LEFT(B3,SEARCH(" ",B3)-1),A3)>0,SEARCH(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1),A3)>0),SUM(IFERROR(SEARCH(LEFT(A3,SEARCH(" ",A3)-1),B3)>0,0),IFERROR(SEARCH(MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",A3,SEARCH(" ",A3)+1)-SEARCH(" ",A3)-1),B3)>0,0),IFERROR(SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3)+1)),B3)>0,0))>1)))),FALSE)

At least on the few examples that have been given , it seems to work correctly.

Basically what is being done is :

1. Check the number of spaces between the words in cell A3.

2. If the number of spaces = 0 , then the two cells A3 and B3 have to match , so check A3=B3.

3. If the number of spaces = 1 , then check if the two words in A3 occur in B3 , by separating the words in A3 and using the SEARCH function ; SEARCH is used because it is case insensitive.

4. If the number of spaces in A3 = 2 , it means there are 3 words in A3 ; check if there are 2 words in B3 ; if yes , then separate the words in B3 and match them in A3.

5. If the number of words in both A3 and B3 is 3 , then see if at least 2 words in A3 match those in B3.

Narayan

P.S. : I am sure this can also be improved upon.

oldchippy · Mar 19, 2012

@Narayank991,

Well done, I knew someone would improve on mine

santoshsapre · Mar 19, 2012

Dear all,

Hats off to you all!!! Thanks for your all efforts.

Dear NARAYANK991,

its works!! can we add this in to macro.

Thanks once again.

santoshsapre · Mar 19, 2012

Dear Narayan

have tried on my main data and observed that below two names are differ only one common name is Bajpai but still result showing as TRUE its should be false.

columen A column B Result

MANJRI LADA BAJPAI R N BAJPAI TRUE

pls advise

oldchippy · Mar 19, 2012

Hi santoshsapre,

Had you tried mine, it shows FALSE as you require

NARAYANK991 · Mar 20, 2012

Hi Santosh ,

Let me revise it ; the problem is that the letter "R" is matching the letter "R" in the word "MANJRI" ! I need to add a check to ensure that initials are not matched with words.

You can continue to use oldchippy's formula , since it takes care of this.

Narayan

P.S. What should be the result if the initials were M L ? I think you should concentrate on bringing in more standardization into data entry rather than doing this kind of validity checking after non-standard data has been entered !

NARAYANK991 · Mar 20, 2012

Hi Santosh ,

You can try out this formula , where a check to see if any "word" is more than 1 character long ( to exclude matching initials ) has been incorporated. Again , only checking it out with a lot of data will "prove" that it is sufficient for your purposes.

=IFERROR(IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=0,A3=B3,

IF(LEN(A3)-LEN(SUBSTITUTE(A3," ",""))=1,

AND(SEARCH(LEFT(A3,FIND(" ",A3)-1),B3)>0,SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3)-1),B3)>0,LEN(LEFT(A3,FIND(" ",A3)-1))>1,LEN(RIGHT(A3,LEN(A3)-SEARCH(" ",A3)-1))>1),

IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,

AND(SEARCH(LEFT(B3,SEARCH(" ",B3)-1),A3)>0,SEARCH(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1),A3)>0,LEN(LEFT(B3,FIND(" ",B3)-1))>1,LEN(RIGHT(B3,LEN(B3)-SEARCH(" ",B3)-1))>1),

AND(SUM(IFERROR(AND(LEN(LEFT(A3,SEARCH(" ",A3)-1))>1,SEARCH(LEFT(A3,SEARCH(" ",A3)-1),B3)>0),0),

IFERROR(AND(LEN(MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",A3,SEARCH(" ",A3)+1)-SEARCH(" ",A3)-1))>1,SEARCH(MID(A3,SEARCH(" ",A3)+1,SEARCH(" ",A3,SEARCH(" ",A3)+1)-SEARCH(" ",A3)-1),B3)>0),0),

IFERROR(AND(SEARCH(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3)+1)),B3)>0,LEN(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3)+1)))>1),0))>1)))),FALSE)

Narayan

santoshsapre · Mar 20, 2012

Dear OC,

have tried your formula and getting correct result as required...many thanks!!

Dear Narayan,

now your revised formula working correct as required. Thanks once again!!

one more help required. how to add this formula in macro??

NARAYANK991 · Mar 21, 2012

Hi Santosh ,

Thanks for the feedback.

I have not understood your last question ; which macro do you want to add this formula to , and why ? The formula is to be entered in cells in the column next to your data columns.

Or are you thinking of doing the whole validation using a macro ? In such case , this formula can be replaced by VBA code , which will be much simpler and more elegant , since more validation checks can be built into the code.

Narayan

santoshsapre · Mar 21, 2012

Dear Narayan,

the reason for the macro is that the validation checking is done by my junior and i don't want to show them formula to avoid any wrong result by changing modification ("UNGLY")in the formula by them which may trouble for me.

if possible pls try to provide macro.

Thanks!

Luke M · Mar 21, 2012

Lengthy, but here's a UDF that I believe accomplishes the goal. Worksheet formula would then just be:

=CompareNames(A2,B2)

[pre]

Code:

Function CompareNames(Name1 As String, Name2 As String) As Boolean
Dim Name1x As String
Dim Name1y As String
Dim Name1z As String
Dim Name2x As String
Dim Name2y As String
Dim Name2z As String

Name1 = UCase(Name1)
Name2 = UCase(Name2)

&#39;Parse the strings for Name 1
Name1x = Left(Name1, WorksheetFunction.Find(&#34; &#34;, Name1) - 1)

If Len(Name1) - Len(WorksheetFunction.Substitute(Name1, &#34; &#34;, &#34;&#34;)) = 2 Then
Name1y = Left(Mid(Name1, WorksheetFunction.Find(&#34; &#34;, Name1) + 1, 999), WorksheetFunction.Find(&#34; &#34;, _
Mid(Name1, WorksheetFunction.Find(&#34; &#34;, Name1) + 1, 999)) - 1)
Name1z = Mid(Name1, WorksheetFunction.Find(&#34; &#34;, Name1, WorksheetFunction.Find(&#34; &#34;, Name1) + 1) + 1, 999)
Else
Name1y = Mid(Name1, WorksheetFunction.Find(&#34; &#34;, Name1) + 1, 999)
End If

&#39;Parse the strings for Name 2
Name2x = Left(Name2, WorksheetFunction.Find(&#34; &#34;, Name2) - 1)

If Len(Name2) - Len(WorksheetFunction.Substitute(Name2, &#34; &#34;, &#34;&#34;)) = 2 Then
Name2z = Mid(Name2, WorksheetFunction.Find(&#34; &#34;, Name2, WorksheetFunction.Find(&#34; &#34;, Name2) + 1) + 1, 999)
Name2y = Left(Mid(Name2, WorksheetFunction.Find(&#34; &#34;, Name2) + 1, 999), WorksheetFunction.Find(&#34; &#34;, _
Mid(Name2, WorksheetFunction.Find(&#34; &#34;, Name2) + 1, 999)) - 1)
Else
Name2y = Mid(Name2, WorksheetFunction.Find(&#34; &#34;, Name2) + 1, 999)
End If

&#39;Fill with dummy names
If Name1z = &#34;&#34; Then
Name1z = &#34;xxxxxxxxxxxxxxxxxx&#34;
End If
If Name2z = &#34;&#34; Then
Name2z = &#34;zzzzzzzzzzzzzzzzzz&#34;
End If

Dim x As Integer
x = 0
&#39;Compare possible combinations
If Name1x = Name2x Or Name1x = Name2y Or Name1x = Name2z Then
x = x + 1
End If

If Name1y = Name2x Or Name1y = Name2y Or Name1y = Name2z Then
x = x + 1
End If

If Name1z = Name2x Or Name1z = Name2y Or Name1z = Name2z Then
x = x + 1
End If

CompareNames = (x &#62;= 2)

End Function

[/pre]

oldchippy · Mar 21, 2012

@Luke,

"some Guru could shorted it to about 20 characters probably".....19 in fact! Plus a nice UDF......hats off

MATCH 2 column with diffrent name format

New Member

Member

New Member

Excel Ninja

Active Member

New Member

Member

Member

Active Member

Member

New Member

Active Member

Member

Excel Ninja

Active Member

New Member

New Member

Active Member

Excel Ninja

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

Active Member