Compare data in seperate columns to find likely matches

Solved/Closed
Spraoi
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013
- Feb 15, 2013 at 07:34 AM
Spraoi
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013
- Feb 18, 2013 at 09:44 AM
Hello,
I want to compare data in columns which essentially is the same but worded different.

For Example

Name of Person County County District
ABC Co Dublin County Dublin
EFG Dublin 1 County Dublin
HIK Cork County Kerry
LMF CO LIMERICK CORK


Both of those first two records relate to people living in Dublin but the geographical unit can be represented differently.

Is there a formula I can use to find matches? And secondly then a way to easily see those that are mismatched like the bottom two records

regards
Spraoi

4 replies

Zohaib R
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Feb 15, 2013 at 08:17 AM
Hi Spraoi,

A little clarification is needed with respect to your example. You have mentioned there are two records in your example. However, due to formatting the data looks jumbled. Please upload a sample file to the following website:

https://authentification.site

Note: Before you upload any files please make sure there is no personal or confidential information contained in it.

Please do write back to us.
0
Spraoi
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013

Feb 15, 2013 at 08:39 AM
Thanks Zohaib

i've uploaded a sample file at this link.

http://speedy.sh/4Py37/Sample-table-for-mismatched-records-and-finding-out-what-could-be-a-match.xlsx


regards
Spraoi
0
Zohaib R
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Feb 18, 2013 at 06:24 AM
Hi Spraoi,

You can use conditional formatting to highlight column values which have same keywords such as "Dublin" or "Cork". For example to enter conditional formatting to highlight all the cells that contain the keyword "Dublin" use the following method:

1. Select the cells where you want to apply the Conditional Formatting.
2. Click Conditional Formatting from the top ribbon.
3. Select New Rule.
4. Click Use a formula to determine which cells to format.
5. Enter the below mentioned formula in Format values where this formula is true: =ISNUMBER(SEARCH("Dublin",B2))=TRUE
6. You will see all the cells which have the word "Dublin" in it will be highlighted.
7. Repeat steps 1 through 6 for keyword "Cork".

I have uploaded the sample file here:

http://speedy.sh/GEYPU/Copy-of-Sample-table-for-mismatched-records-and-finding-out-what-could-be-a-match-1.xlsx

Please revert for clarification.
0
Spraoi
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013

Feb 18, 2013 at 09:44 AM
thanks very much Zohaib, you have made my life a lot easier. that is a great help
regards
Spraoi
0