How do I isolate all company addresses ?

[Maverick27]

Attn all Excel Gurus

I have an Excel sheet of 500K Email Addresses.

How do isolate all company addressess eg addresses which DON'T have the following domains ?

@Yahoo ;
@gmail ;
@Hotmail ;
@Live ;
@ymail ;
@msn

Anyone care to share a Excel function or Macro to filter out the company address domains.

Below is a screenshot of the Excel list.

Thanks in Advance.

 

[BSALV]

I've run some tests on 300k rows of data & it definitely doesn't work, I only end up with about 34k rows visible when there should be a lot more.

i think transpose works like mod 65.536, for example 300,000 rows => 300,000 mod 65536 = 37,856

 

[Fluff]

But you didn't get an error?

No error, just filtered out a lot of rows that should be visible.

This method is not based on partial matches. With the filter(filter(filter method, it also finds "@live.uk.nl" because the word "@Live" is found

Yes but the Filter method will also filter out something like @Livery_Stables.com

 

[JEC]

Yes of course, could be enough if there aren't email addresses like that. The Regex will do, but the transpose limit ruins it again

 

[BSALV]

+1.000.000 rows, 0,4 sec
perhaps add an extra "." for extensions "hotmail.com", "hotmail.be", etc

FL = Filter(FL, it [U][I][B]& "."[/B][/I][/U], 0, vbTextCompare)

(tried to underline and bold the & "." but didn't work out)

Sub TestPlus65000()
     Dim b()   As Variant
     a = ActiveSheet.UsedRange.Resize(, 2)
     ReDim b(1 To UBound(a))
     For i = 1 To UBound(a): b(i) = a(i, 1): Next
     FL = Filter(b, "@", 1, vbTextCompare)

     For Each it In Array("@Yahoo", "@gmail", "@Hotmail", "@Live", "@ymail", "@msn")
          If UBound(FL) = -1 Then Exit For
          FL = Filter(FL, it, 0, vbTextCompare)
     Next
     If UBound(FL) = -1 Then
          MsgBox "nothing left"
     Else
          MsgBox Join(FL, vbLf), vbInformation, UCase("emailaddresses left")
     End If

End Sub

 

[Maverick27]

+1.000.000 rows, 0,4 sec
perhaps add an extra "." for extensions "hotmail.com", "hotmail.be", etc

FL = Filter(FL, it [U][I][B]& "."[/B][/I][/U], 0, vbTextCompare)

(tried to underline and bold the & "." but didn't work out)

Sub TestPlus65000()
     Dim b()   As Variant
     a = ActiveSheet.UsedRange.Resize(, 2)
     ReDim b(1 To UBound(a))
     For i = 1 To UBound(a): b(i) = a(i, 1): Next
     FL = Filter(b, "@", 1, vbTextCompare)

     For Each it In Array("@Yahoo", "@gmail", "@Hotmail", "@Live", "@ymail", "@msn")
          If UBound(FL) = -1 Then Exit For
          FL = Filter(FL, it, 0, vbTextCompare)
     Next
     If UBound(FL) = -1 Then
          MsgBox "nothing left"
     Else
          MsgBox Join(FL, vbLf), vbInformation, UCase("emailaddresses left")
     End If

End Sub

Output

 

[JEC]

After all, you can use a helper column.
Pull this down and then filter for false in that column

=ISNUMBER(LOOKUP(100,SEARCH({"@Yahoo.","@gmail.","@Hotmail.","@Live.","@ymail.","@msn."},A2)))

 

[mohadin]

Sorry - the code is giving an Error.View attachment 55389

Try to match sheet name (your sheet name and the sheet name in the code)
Good luck

 

[Maverick27]

Thanks everyone !!

 

[Fluff]

Glad we could help & thanks for the feedback.