Using Instr to compare 2 fields

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
Hello,

I need a query that will compare the 'Work Trading Name' to the 'Email1' field. I want to find all emails that do not have a similar domain.

<TABLE style="WIDTH: 543pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=723 border=0><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-LEFT-COLOR: #d4d0c8; WIDTH: 543pt; BORDER-TOP-COLOR: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" width=723 colSpan=5 height=17>CORE

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: silver" height=20>First Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Last Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Team Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Work Trading Name</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT-COLOR: black; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: silver">Email1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Rey</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">ING INVESTMENT MANAGEMENT AMERICAS</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@inginvestment.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Andrés</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">MON GESTIO</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@andbanc.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abhijit</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BLUEBAY ASSET MANAGEMENT</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@globop.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michelle</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BROADMARK ASSET MANAGEMENT</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@broadmarkasset.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Michael</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">WADDELL & REED</TD><TD class=xl69 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@waddell.com</TD></TR></TBODY></TABLE>



In the results I would like the following:


<TABLE style="WIDTH: 543pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=723 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 212pt; mso-width-source: userset; mso-width-alt: 10313" width=282><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=74 height=20>Andrés</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 71pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=95>abc</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 61pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=81></TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 212pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=282>MON GESTIO</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; WIDTH: 143pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=191>abc@andbanc.com</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abhijit</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">BLUEBAY ASSET MANAGEMENT</TD><TD class=xl68 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-LEFT-COLOR: silver; BORDER-TOP-COLOR: silver; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent">abc@globop.com</TD></TR></TBODY></TABLE>



I tried the one below but was unable to get it to work properly.

Code:
select core.*
from core
WHERE instr(1,core.[Work Trading Name],core.[email1],1)=0
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,394
Office Version
  1. 365
The key word in your post is similar. What do you mean by that, exactly?



Rich (BB code):
select core.*
from core
WHERE 
InStr([email1],Mid(Replace([Work Trading Name]," ",""),1,5))=0


This will select records where

after removing spaces form the [Work Trading Name]
try to find the string of the first 5 characters of the [Work Trading Name]
anywhere in the [email1]

If you want to change the length of the string to match
change the 5 with your choice.
 
Last edited:

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576
I went ahead and created two functions: one to get the company name out of the email address and one to strip spaces.

Code:
Public Function GetCompanyEmailName(sEmail As String) As String
'return the company name from an email address
Dim temp As String
temp = Split(Split(sEmail, "@")(1), ".com")(0)
GetCompanyEmailName = temp
End Function

Public Function StripSpaces(str As String) As String
StripSpaces = Replace(str, " ", "")
End Function

These are called from the query like in jack's reply.

Code:
SELECT Core.*
FROM Core
WHERE (((InStr(StripSpaces([WorkTradingName]),GetCompanyEmailName([Email1])))=0));

hth,

Rich
 

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
Hmm im having trouble making the function work Revans. Im trying to insert it has a module (the same way i would do a normal macro) but i cant seem to get it to work.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Couldn't you use the Like operator?
 

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576
By "macro" I hope you mean VBA; I haven't written a macro in ... 12+ years. Open up a new module and paste them in there. Save the module with whatever name you like, "Module1", "basUtility", or something.

I suppose Like would work too, but you'd still need to take out the spaces so the email domain would match. I like the function route because these kinds of requirements have a way of creeping and it's easier to play in a function than in a query, imo. Mileage varies.

hth,

Rich
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Rich

Agree about the code but most text functions that are available in VBA are also available for expressions.

Not all of course.:)
 

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
By "macro" I hope you mean VBA; I haven't written a macro in ... 12+ years. Open up a new module and paste them in there. Save the module with whatever name you like, "Module1", "basUtility", or something.

I suppose Like would work too, but you'd still need to take out the spaces so the email domain would match. I like the function route because these kinds of requirements have a way of creeping and it's easier to play in a function than in a query, imo. Mileage varies.

hth,

Rich

Yes, thats what i meant. Ive copied the code into a new module but when I select run, A box appears that asks me for the name of a macro.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Yes, thats what i meant. Ive copied the code into a new module but when I select run, A box appears that asks me for the name of a macro.
That sounds strange. What exactly does the message say ... ? (I'm afraid I don't really believe it says "What is the name of a macro?" ;) )
 

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576
That's what Run does; I've never found it all that useful. You'll want to call the function from your query like this

Code:
SELECT Core.*
FROM Core
WHERE (((InStr(StripSpaces([WorkTradingName]),GetCompanyEmailName([Email1])))=0));

hth,

Rich
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,214
Messages
5,836,045
Members
430,402
Latest member
bcurran2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top