Solved

similar data

Posted on 2011-09-14
5
172 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
can you send me some example data
0
 
LVL 10

Expert Comment

by:ukerandi
Comment Utility
if you can give some examples i can create formulae
0
 
LVL 3

Author Comment

by:pma111
Comment Utility
Sorry its commercially sensitive
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now