VLOOKUP connecting cells that have slightly different spellings

[poonam.ss]

Hello,
I have 2 Excel Sheets namely (1) Office Mis (2) Client MIS

In Office MIS we have Sr. No. & Name as follows:
Sr. Customer Name
01 - Mr. Mr. Santosh Pashupati Jha & Mrs. Priti Santosh Jha - Nov (33)
02 - Mrs. Manjusha Ladoba Lad & Mr. Ladoba Ramchandra Lad - Jan (07)
03 - Meghana M. Naik & Mr. Mangesh M. Naik - Jan (06)
04 - Mrs. Ashabai Bitu Dange & Mr. Bitu Tatoba Dange - Jan (08)
05 - Mr.Gangadhar Vaman Gosavi - Jan (01)
06 - Mr. Ganesh Shivaji Gopal - Jan (02)
07 - Mr. Milind Salvi & Lalita Salvi - Jan (03)
08 - Mrs. Kusum Trilokinath Pandey - Jan (04)
09 - Mrs. Neelam Sachinkumar Gupta & Mr. Sachinkumar Ramprasad Gupta - Dec (63)
10 - Mr. Yogesh Ambadas Jagtap - Dec (52)

In Client MIS we have similar names and Appl No. as follows:

App. No. Customer Name

100454 Mrs. Shobha B. Rokade & Mr. Ketu B. Rokad
101880 Mr. Vishal H. Nikam & Mrs. Sheetal V. Nikam
102271 Mr. Praveenkumar Gurucharan Gupta
95584 (1) Mrs. Sonali Pritesh Shinde (2) Mr. Pritesh K. Shinde (3) Mrs. Shakuntala K. Shinde
99969 (1) Mr. Arun Parshuram Satale & (2) Mrs. Soravi Arun Satale
100456 Mr. Chadrama S. Yadav & Mr. Karamchand C. Yadav
101722 Mr. Sanjeevan Jyoti Bose
101108 Mr. Ramasare Shobhnath Gupta
97830 Mrs. Chandrakala A. Singh

Now we want to compare the names which are slight different and put App. No. in Office MIS.

This data runs into 500 names. So we need to compare this data and bring the data in Office MIS. Can you please tell me the formula.

Thanks Poonam

[JohnTopley]

There is no easy formula to do this sort of check: your sample does not appear to have any matches using the human eye!

In both lists there is extraneous data to be removed e.g Dates [Jan (30)] on "Office MIS" and numbers [ (n) ] in "Client MIS" before any comparison could be done.

You will need VBA rather than formulae to try to get a match..

[poonam.ss]

Thank you for aprompt reply, can you help with VBA or any other solution as removing Dates [Jan (30)] on "Office MIS" and numbers [ (n) ] in "Client MIS" before any comparison is very time consuming.

[JohnTopley]

Attach a sample workbook with 20-30 rows of data in both sheets


Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on "REPLY" then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.


I will try to look at this over the weekend.

[poonam.ss]

I have attached the Excel with three sheets (1) Office MIS (2) Client MIS & (3) Desired Result in Office MIS. I have done it maunally for you.

[JohnTopley]

See the attached.

I have used formula to remove much of the "extraneous" data as practcical.

In "Office MIS"

column E

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B2,8,255),"(",""),")",""),"1",""),"2",""),"3",""),"-",""),"Mr.",""),"Mrs.",""))

In "Client MIS"

in Column D

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"(",""),")",""),"1",""),"2",""),"3",""),"Mr.",""),"Mrs.",""))

in "Office MIS"

in column G

Copied data from column D of "Client MIS"

Column F

=IFERROR(IF(MATCH(E2,$G$2:$G$20,0),"Matched",""),"")

Matches columns E & G

in column G I have highlighted in RED why there is no MATCH.

Hopefully you can use this approach to assists in finding the matches: you will not get a 100% match.

Now signing off for the day (night!)