characters which are matching between Data 1 & Data 2
 

Dear all,

My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B.
I want to look up matching results of such names in Coloum C saying Match.
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match

I request you'll to help me to come out of this problem.

Thanks in advance.

Karan.

characters which are matching between Data 1 & Data 2
 

try this

put this formula in C2 and drag it down

=IF(ISNA(MATCH("*"&A2&"*",$B$2:$B$100,0)),"","matc h")




On Oct 24, 2:48*pm, Karan wrote:
Dear all,

My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B. *
I want to look up matching results of such names in Coloum C saying Match.. *
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM *
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR *
Data2(Col B) D DASTAGIRI SAB * *
Col C should Show Match

I request you'll to help me to come out of this problem.

Thanks in advance.

Karan.

characters which are matching between Data 1 & Data 2
 

One try
In C1: =IF(COUNTIF(B1,"*"&A1&"*"),"Match","")
Copy C1 down. That should flag it as required provided the strings in col A
are as per your 1st example. Your 2nd example type doesn't look possible to
achieve.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Karan" wrote:
My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B.
I want to look up matching results of such names in Coloum C saying Match.
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match

characters which are matching between Data 1 & Data 2
 

Dear Muddan Madhu,

I will let you know what i understood from your command.
A1 is a match with any one of B coloum names it shows Match. But i want
which row or B coloum watches with A1.

Now i feel that my question was itself not clear. I will explain it once
again:

I have two sets of names in col A and Col B. I want to find any part of
name from either coloum matches, if yes it should give result A1 and B63
matches.

I feel it should search with part of the character to get this result.

Once again, thanks for your valuable help.

Karan.

"muddan madhu" wrote:

try this

put this formula in C2 and drag it down

=IF(ISNA(MATCH("*"&A2&"*",$B$2:$B$100,0)),"","matc h")




On Oct 24, 2:48 pm, Karan wrote:
Dear all,

My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B.
I want to look up matching results of such names in Coloum C saying Match..
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match

I request you'll to help me to come out of this problem.

Thanks in advance.

Karan.

characters which are matching between Data 1 & Data 2
 

Dear Max,

Thanks for your reply.

I am not able to get the desired result. Your command searches only the
same row of the two given coloums. I think it should be One to Many search.
The problem is data is not arranged properly. hence i need to search a part
of the name with next coloum. I know i can't find the exact name, but it
should narrow the search to assist me finding the proper name.

I don't know if i am confusing you'll. Please Help.

Karan.

"Max" wrote:

One try
In C1: =IF(COUNTIF(B1,"*"&A1&"*"),"Match","")
Copy C1 down. That should flag it as required provided the strings in col A
are as per your 1st example. Your 2nd example type doesn't look possible to
achieve.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Karan" wrote:
My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B.
I want to look up matching results of such names in Coloum C saying Match.
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match

characters which are matching between Data 1 & Data 2
 

The problem is data is not arranged properly ..

Ah, guess your orig. post's data representation/descript
might have misled me

Try in C1, copied down:
=IF(A1="","",IF(COUNTIF(B:B,"*"&A1&"*"),"Match","" ))
Same provisos though. It won't pick up your 2nd example
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

characters which are matching between Data 1 & Data 2
 

This extract's results might be of better use to you

In C1, normal ENTER:
=IF(A1="","",IF(ISNA(MATCH(TRUE,INDEX((ISNUMBER(SE ARCH(A1,B$1:B$1000))),),0)),"",INDEX(B$1:B$1000,MA TCH(TRUE,INDEX((ISNUMBER(SEARCH(A1,B$1:B$1000))),) ,0))))

Copy C1 down to the last row of data in col A. Adapt the ranges to suit the
extent of your data in col B. Col C will extract col B's "fuzzy matches"
corresponding to the values in col A.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

characters which are matching between Data 1 & Data 2
 

try this

in C1 put this formula and drag it down

=INDEX("A"&ROW(A1)&" B"&ROW($B$1:$B$5),MATCH("*"&A1&"*",$B$1:$B$5,0) ,
0)



On Oct 24, 4:02*pm, Karan wrote:
Dear Max,

Thanks for your reply.

I am not able to get the desired result. *Your command searches only the
same row of the two given coloums. *I think it should be One to Many search. *
The problem is data is not arranged properly. *hence i need to search a part
of the name with next coloum. *I know i can't find the exact name, but it
should narrow the search to assist me finding the proper name.

I don't know if i am confusing you'll. *Please Help.

Karan.



"Max" wrote:
One try
In C1: =IF(COUNTIF(B1,"*"&A1&"*"),"Match","")
Copy C1 down. That should flag it as required provided the strings in col A
are as per your 1st example. Your 2nd example type doesn't look possible to
achieve.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Karan" wrote:
My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B. *
I want to look up matching results of such names in Coloum C saying Match. *
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD * * *
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR * * * *
Data2(Col B) D DASTAGIRI SAB *
Col C should Show Match- Hide quoted text -

- Show quoted text -

characters which are matching between Data 1 & Data 2
 

Dear Max,

Thanks a lot for your invaluable help. It works !!!

And one Small Clarification is needed. The output name shows the first
result found on Coloum B.
Like if Col A has John 3 times, the result of the command shows the first
result found in Coloum B that John Wade against all the 3 rows containing
John. Can it be corrected.

What you have given already is a great help to me. Thanks once again.

Karan.

"Max" wrote:

This extract's results might be of better use to you

In C1, normal ENTER:
=IF(A1="","",IF(ISNA(MATCH(TRUE,INDEX((ISNUMBER(SE ARCH(A1,B$1:B$1000))),),0)),"",INDEX(B$1:B$1000,MA TCH(TRUE,INDEX((ISNUMBER(SEARCH(A1,B$1:B$1000))),) ,0))))

Copy C1 down to the last row of data in col A. Adapt the ranges to suit the
extent of your data in col B. Col C will extract col B's "fuzzy matches"
corresponding to the values in col A.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

characters which are matching between Data 1 & Data 2
 

Dear Muddan Madhu

Thanks for your reply.

It gives me an error message #N/A

No results were found.

Thanks

Karan.

"muddan madhu" wrote:

try this

in C1 put this formula and drag it down

=INDEX("A"&ROW(A1)&" B"&ROW($B$1:$B$5),MATCH("*"&A1&"*",$B$1:$B$5,0) ,
0)



On Oct 24, 4:02 pm, Karan wrote:
Dear Max,

Thanks for your reply.

I am not able to get the desired result. Your command searches only the
same row of the two given coloums. I think it should be One to Many search.
The problem is data is not arranged properly. hence i need to search a part
of the name with next coloum. I know i can't find the exact name, but it
should narrow the search to assist me finding the proper name.

I don't know if i am confusing you'll. Please Help.

Karan.



"Max" wrote:
One try
In C1: =IF(COUNTIF(B1,"*"&A1&"*"),"Match","")
Copy C1 down. That should flag it as required provided the strings in col A
are as per your 1st example. Your 2nd example type doesn't look possible to
achieve.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"Karan" wrote:
My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last & first
Name or First name initials and Last Name & so on) in Coloum A and Coloum B.
I want to look up matching results of such names in Coloum C saying Match.
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match- Hide quoted text -

- Show quoted text -

characters which are matching between Data 1 & Data 2
 

Hi

Try this.

IF(ISNUMBER(LOOKUP(2,1/SEARCH(A1,B1,1))),"Found","Not found")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"muddan madhu" wrote in message
...
try this

put this formula in C2 and drag it down

=IF(ISNA(MATCH("*"&A2&"*",$B$2:$B$100,0)),"","matc h")




On Oct 24, 2:48 pm, Karan wrote:
Dear all,

My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual
or
person written in different format (like first name last name or last &
first
Name or First name initials and Last Name & so on) in Coloum A and Coloum
B.
I want to look up matching results of such names in Coloum C saying
Match.
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in
same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match

I request you'll to help me to come out of this problem.

Thanks in advance.

Karan.

characters which are matching between Data 1 & Data 2
 

Hi.

Try this

IF(ISNUMBER(LOOKUP(2,1/SEARCH(A1,B1,1))),"Found","Not found")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Karan" wrote in message
...
Dear all,

My query is similar to Vlookup and as follows:
I have Data1 and Data2 both containing Complete Name of same individual or
person written in different format (like first name last name or last &
first
Name or First name initials and Last Name & so on) in Coloum A and Coloum
B.
I want to look up matching results of such names in Coloum C saying Match.
There is 25000 records and not possible manually.

Can someone suggest me that, is there any way i can sort out any 3 or 4
characters from each data. J Sridhar & Sridhar J Should be sorted out in
same
row or can show in next coloum as match.

For Example
Data1(Col A) ATHESHAM
Data2(Col B) DR.ATHESHAM AHEMAD
Col C should Show Match (if not blank)

Data1(Col A) DASTHAGIR
Data2(Col B) D DASTAGIRI SAB
Col C should Show Match

I request you'll to help me to come out of this problem.

Thanks in advance.

Karan.

characters which are matching between Data 1 & Data 2
 

Thanks a lot for your invaluable help. It works !!!
Glad it did. Could you mark that response as helpful by pressing the Yes
buttons (like the ones below)?

.. Can it be corrected
That's the way it functions
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---