Compare data in seperate columns to find likely matches

Solved/Closed
Report
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013
-
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013
-
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

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
53
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.
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013

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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
53
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.
Posts
3
Registration date
Friday February 15, 2013
Status
Member
Last seen
February 18, 2013

thanks very much Zohaib, you have made my life a lot easier. that is a great help
regards
Spraoi