CODEWITHSUNDEEP
sql-serverexcelvba
Nonagon
Nonagon

Reputation: 407

Check if values in 2 different columns contain a match

I have 2 columns in an Excel file (I pulled them from 2 different SQL views on 2 different Servers). That should contain the same data. Example:

Name               Name
Adam Smith         Adam Smith
Billy Jones        Bill Jones
Colin Richards     Colin Richards
Dale Cooper        Dal Coop

I want to be able to check if each column contains exact matches or partial matches of these names, problem is there is 1000+ names in each column

I have tried VLOOKUP but i don't think I'm doing the logic right. Are their any other ways?

EDIT

My VLOOKUP logic i tried was this

=VLOOKUP(B2,A:A,1,TRUE)

I was trying to match the first value in Column B to match the entire Column of Column A and show as TRUE, that is what i tired

Upvotes: 0

Views: 458

Answers (1)

Prafulla
Prafulla

Reputation: 585

You can use SOUNDEX in sql server. It returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. Since you are checking for exact or the partial string match SOUNDEX is a good choice. See soundex documentation https://msdn.microsoft.com/en-us/library/ms187384.aspx. I have used soundex for your query and it is giving expected result.

Code as below

create table #one(name1 varchar(30),name2 varchar(30))
insert into #one values ('Adam Smith','Adam Smith')
insert into #one values ('Billy Jones','Bill Jones')
insert into #one values ('Colin Richards','Colin Richards')
insert into #one values ('Dale Cooper','Dal Coop')

select *, SOUNDEX(name1) name1_soundex, SOUNDEX(name2) name2_soundex 
from #one where 
SOUNDEX(name1) = SOUNDEX(name2)

Result

If you want to use soundex in MS Excel, refer this question

http://stackoverflow.com/questions/14700074/how-to-find-the-match-between-text-that-typo-in-excel

Upvotes: 2

Related Questions