 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2006Two lists, find *similar* cells (not duplicates) and mark/highlight, etc.
So I have 2 lists with client names. Since different salespeople entered each data cell, the client names are spelled differently in many of the entries. For example, some list client Bill Smith as: "B Smith" "B. Smith" "Bill S." "Bill Smith"...you get the idea. I want to locate (and eventually manipulate) these for *each* client. Since there are hundreds of clients, I can't just use a CountIf formula (for example) that looks for ranges that start with B or Bill, because I'd have to create one for each client (e.g. now look for ranges that start with S for Susan). Is there a formula (or maybe I'd need a macro) that would: look at its corresponding cell (formula in C2 would look at A2) and take the first 3 letters (or 2 letters, or 5 letters) and match those with the clientnames (cells) from the second list (in columb B)? Please clarify what data is in column A and in column B so your last para is more meaningful. By the way is there a column with zipcodes or phone numbers? To avoid this in future, use separate columns for fist and last name; you can always concatenate with the & operator ( =A2&" "&B2) - ain't hindsight great! best wishes  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > So I have 2 lists with client names. Since different salespeople > entered each data cell, the client names are spelled differently in [quoted text clipped - 11 lines] > 3 letters (or 2 letters, or 5 letters) and match those with the > clientnames (cells) from the second list (in columb B)? Thanks Bernard. So column A has existing client names for the last 5 years. Column B has the client names that have done NEW business in the last 6 months (but not necessarily by new clients). So the names in column B are a mix of 1.) new names (that don't appear in list A) and 2.) existing ones (that do appear in list A). I'm trying to find out which names from the list of new business (column B) are existing clients that are listed in column A (but might be typed in differently--e.g. B. Smith instead of Bill Smith). > Please clarify what data is in column A and in column B so your last para is > more meaningful. [quoted text clipped - 23 lines] > > 3 letters (or 2 letters, or 5 letters) and match those with the > > clientnames (cells) from the second list (in columb B)? Addresses, or phone numbers may be easier to identify....there are just too many different possibilities with the names........you could get there eventually maybe by using something like =left(A1,5) down the left side of your lookup table and then something like =VLOOKUP(left(A1,5),MyTable,1,false) to do the search....but it will still take much hand intervention before it's over...........or maybe using the AutoFilter and filtering for "contains Smith", might help hth Vaya con Dios, Chuck, CABGx3 > So I have 2 lists with client names. Since different salespeople > entered each data cell, the client names are spelled differently in [quoted text clipped - 11 lines] > 3 letters (or 2 letters, or 5 letters) and match those with the > clientnames (cells) from the second list (in columb B)?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.