Comparing two excel columns with MS Query

[chris2005]

Hi there,

I would like to compare two excel columns like:

testab testbb
testaa testabbb
testcc testaaff

As you can see testab and testaa (reg)matches two times.

With what sql query using MS Query can I retrieve those matches?

Note that I want to get the matches based on LIKE statements.

Pls help.

Thanx in advance.

Regards,
Chris

 

[jjturner]

Welcome!

why not just use VLookups?

 

[chris2005]

Welcome!

why not just use VLookups?

Since I understood those are exact matches only or less then or more then statements. I need LIKE statements.

 

[jjturner]

VLookups can be frustrating, but they don't need an exact match.

Anyway, for MS Query, you'd use something like this:

SELECT DISTINCT [T1].[FieldName1],
(SELECT Count([T2].[FieldName2])
 FROM myDataList T2
 WHERE [T2].[FieldName2] Like [T1].[FieldName1] & "*") AS NumberOfMatches
FROM myDataList T1

Not tested, but that would be my approach.

HTH,
John