Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

similar data

Posted on 2011-09-14
5
Medium Priority
?
182 Views
Last Modified: 2012-08-14
What is the best way to identify similar data when comparing 2 columns.

I have one sheet of data that has been matched together from 2 seperate databases on postal/zip code of businesses. So where the zip code matches I have address 1 and name from two databases joined on where zip code matched, and then input into excel.

There are two columns per database I need to match to get a true match but there are some typos in one database that means I am not getting true matches.

I have been doing IF statements where if (column A - which was the output of DB A businesses addresses matches exactly to column C - which was the output of DB B business addressed, then type "match" in column E).

Also in column F I do an IF statement for columns B and D for exact matches, this time it is business address line 1 - so if they match exact then "match", if not then "not".

However when reviewing the nots I see some minor typos in database B which means they are matches, but a character is slightly wrong, or theres a comma added by mistake.

How can I identify "likely to be matched with typos"?

Is there anyway to say "1 character differs", or "2 characters differ" or any other ideas?
0
Comment
Question by:pma111
  • 3
  • 2
5 Comments
 
LVL 10

Accepted Solution

by:
ukerandi earned 2000 total points
ID: 36535244
first you need to write following kind of Formula to remove ","
=IF(FIND(",",A1)>=1,REPLACE(A1,FIND(",",A1),1," "))
then after that write match if condition
0
 
LVL 3

Author Comment

by:pma111
ID: 36535258
Its not just commas thought that's just one of many cases other times it can be one or two characters wrong
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36536911
can you send me some example data
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36536921
if you can give some examples i can create formulae
0
 
LVL 3

Author Comment

by:pma111
ID: 36548019
Sorry its commercially sensitive
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question