text formatting and comparing worksheets

lhoffman

New Member
Joined
Oct 11, 2006
Messages
5
I have a two part problem in comparing lists of email addresses. The first should be simple enough: in the C column of one sheet, the addresses are in the format "<name@domain.com>", and I'll need a way to remove the <>'s so that I can compare the list with another one.

The second part seems to come up a bit, most recently addressed by a response by HalfAce to msavage. I tried to use the code HalfAce posted, but the For statement kept giving me a compile error ("Expected: expression"). Here are the specifics to the problem: two worksheets in a book, rows in each are First Name, Last Name, email. I need to compare the entries, and create a third sheet from the rows in Sheet A which are not in Sheet B, using the email add's as the criterium. Any help out there?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Joined
Jul 30, 2006
Messages
3,656
Welcome to the board.

Can we see some examples of the e-mail addresses?
I have a two part problem in comparing lists of email addresses. The first should be simple enough: in the C column of one sheet, the addresses are in the format "", and I'll need a way to remove the <>'s so that I can compare the list with another one."

Can you post the code for "The second part".


Have a great day,
Stan
 

lhoffman

New Member
Joined
Oct 11, 2006
Messages
5
I'm sorry, I did post an example, but all I wound up with were quotes, so I'll write it funny like this:

< name at domain dot com >

There are no spaces in the entries.

As for the code, here is a paste from HalfAce's post, with some elements changed to fit my workbook:

Code:
Sub EmailDifference()
Dim c As Range, List As Object, Rw As Long
Set List = CreateObject("Scripting.Dictionary")

With Sheets("FileMaker")
  For Each c In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    If Not List.Exists(c.Value) Then
      List.Add c.Value, Nothing
    End If
  Next
End With

With Sheets("Outlook")
  For Rw = 2 To .Range("C" & Rows.Count).End(xlUp).Row
    If List.Exists(.Cells(Rw, "C").Value) Then
      .Rows(Rw).Copy Sheets("Differences").Range("A" & Rows.Count).End(xlUp)(2)
    End If
  Next
End With

Set List = Nothing

End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello lhoffman, welcome to the board.
So, am I right in thinking you want to go through and make a list of the addresses in column C
of the Outlook sheet, then go through column C of the FileMaker sheet, and record each address
on the Differences sheet that's not in the list from the Outlook sheet?

(And have you resolved the problem of getting rid of the "<" & ">" characters ?)
- If not, which sheet are these in?
- Am I right in thinking it's column C for both these sheets?
 

lhoffman

New Member
Joined
Oct 11, 2006
Messages
5

ADVERTISEMENT

HalfAce, thanks for responding. Both the Outlook and FileMaker sheets have entries of (Col A) First, (Col B) Last, (Col C) email. The Outlook sheet has entries that aren't in the FileMaker sheet. I want to go through column C of Outlook (the email addresses), and if an entry is not present in column C of FileMaker, I want to take the whole row (First, Last, and email), and add it to the Differences sheet, which is empty.

As for the "<>" characters, I haven't fixed that yet. They are only in the Outlook sheet, and it would be OK to change the entries permanently.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi lhoffman,
Give this a try and see if it's what you're after.
Code:
Sub EmailDifference()
Dim c As Range, List As Object, Rw As Long
Set List = CreateObject("Scripting.Dictionary")

With Sheets("Outlook")
  For Each c In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    c.Value = WorksheetFunction.Substitute(c.Value, "<", "")
    c.Value = WorksheetFunction.Substitute(c.Value, ">", "")
    If Not List.Exists(c.Value) Then
      List.Add c.Value, Nothing
    End If
  Next
End With

With Sheets("FileMaker")
  For Rw = 2 To .Range("C" & Rows.Count).End(xlUp).Row
    If Not List.Exists(.Cells(Rw, "C").Value) Then
      .Rows(Rw).Copy Sheets("Differences").Range("A" & Rows.Count).End(xlUp)(2)
    End If
  Next
End With

Set List = Nothing

End Sub
 

lhoffman

New Member
Joined
Oct 11, 2006
Messages
5

ADVERTISEMENT

When I paste the code into the VB editor, it gives me a compile error at the For statements, saying "Expected: expression". Any idea why?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
:confused: Not off hand.
Can you post the code here exactly as you have it in the module?
 

lhoffman

New Member
Joined
Oct 11, 2006
Messages
5
I just copied the code you posted above and pasted. What's there is exactly what I put in. Could the Excel version matter here?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
I wouldn't think it's a version issue.
It's fine for me in XL 2K and 2003.
If it's a compile error, then some lines of code should be red (yes?)
Which line(s) are those?
 

Forum statistics

Threads
1,136,348
Messages
5,675,241
Members
419,555
Latest member
Paddington

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