Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
Matching one column against another column of data to show the same amount of data. | Excel Worksheet Functions | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |