compare data in seperate columns to find likely matchesSolved/Closed

Question

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

Configuration: Windows 7 / Internet Explorer 9.0

Zohaib R · Answer

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.

Spraoi · Answer

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

Zohaib R · Answer

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.

Spraoi · Answer

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

Compare data in seperate columns to find likely matches

4 responses

Similar discussions

Subscribe To Our Newsletter!