Capture2.JPG
We have imported some data which has been corrupted therefore the spelling is wrong. We have access to the old data but are struggling to match it up with ID number due to incorrect spelling.
We need to match up the correct contact name (Column C) to Column B, either appearing in Column D OR replacing the corrupted name (incorrect spelling) in Column B.
Thanks in advance!
Not really sure what you are matching. Do you have a list of incorrect/correct names to refer to somewhere? or (hopefully) do both sets of names have a unique ID that we can compare with?
It's very hard to work from a picture, a sample sheet makes life much easier. If all you have is what's in the picture, how will excel know what correct name should replace the incorrect name?
Not really sure what you are matching. Do you have a list of incorrect/correct names to refer to somewhere? or (hopefully) do both sets of names have a unique ID that we can compare with?
(Yes the corrupted contact names are in Column B. The correctly spelled names are in column C but are not sorted and do not match the row/cells in column B. This is what we are trying to achieve. The correct spelled name with the ID. Only Column B has the ID number)
It's very hard to work from a picture, a sample sheet makes life much easier. (This is now attached) If all you have is what's in the picture, how will excel know what correct name should replace the incorrect name? (This is why we need help)Find the Formula 1.xlsx
If you have a list of incorrect/correct names then a vlookup will work for you as in the attached sample.
I have made a table of such in columns E & F.
This will work well if you have multiple occurrences of the same name, but if every name is different it won't be of help.
Edit: Why is this file so huge?
The best way to do this is by usng a user defined Function (UDF). The code can be viewed in Module 1 (right click on the Tab name, view code and select module 1.
the formula used is =similarity(B2,$C$2:$C$8) copied down.
Don't forget to enable macros on opening.
Last edited by Glenn Kennedy; 01-08-2016 at 10:05 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Is this the same problem as in ths thread:
http://www.excelforum.com/excel-form...ml#post4282567
If so, please don't duplicate threads. It just causes total confusion. See also Forum rule 5, which you agreed to stick to earlier today!!!
I think the best way to explain is to just attach the whole file.
Really sorry for all the confusion.
CONTACTS WITH IDS.zip
Last edited by Hannah14; 01-08-2016 at 11:10 AM.
!!!!
So what happened to all the mis-spelled entries? Where have they gone to?? In the case of MonikaZysk, there are 4 possible correct answers.
Is this thread about getting multiple answers or making partial matches.
Hi there are some blank cells too for incorrect and it shows the entry in another column for correct ones. i.e. Row No. 405 and 406 has column B and D as blank but column E has entries is this has some relation. If so please specify
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Yes Hemesh. Not all entries in Column E are matched as we had done some cleansing of data what we have left in column B which unfortunately got corrupted. Column E is correctly spelt names although not all will be required to be matched as this is the data BEFORE cleansing. We require the correct spellings to replace column B (or in another column) including the corresponding ID number in column A
Uptill row 87167 the names in column B and Incorrect names in column D are same with some blank cells now you need them to be replaced with new name and ID
is this so
if this is so then use below formula in C2
=IF(B2="","",A2&" "&E2) and drag down
Last edited by hemesh; 01-08-2016 at 12:00 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks