characters which are matching between Data 1 & Data 2

K

Karan

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.
 
M

muddan madhu

try this

put this formula in C2 and drag it down

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

Max

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
 
K

Karan

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.
 
K

Karan

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.
 
M

Max

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
---
 
M

Max

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

In C1, normal ENTER:
=IF(A1="","",IF(ISNA(MATCH(TRUE,INDEX((ISNUMBER(SEARCH(A1,B$1:B$1000))),),0)),"",INDEX(B$1:B$1000,MATCH(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
---
 
M

muddan madhu

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)
 
K

Karan

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.
 
K

Karan

Dear Muddan Madhu

Thanks for your reply.

It gives me an error message #N/A

No results were found.

Thanks

Karan.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Returning a Value from Another Worksheet 2
Match? 5
How to do a lookup using multiple values 4
If not an exact match to left of comma, then ALERT 6
Index Match to add employee number 2
Selcting data from table without repeating plus calc's 2
Macro for comparing columns and highlighting matching/differences 13

Top