+ Reply to Thread
Results 1 to 10 of 10

compare cell to a range

  1. #1
    Registered User
    Join Date
    06-25-2011
    Location
    Portland
    MS-Off Ver
    Excel 2010
    Posts
    4

    compare cell to a range

    I have a column 'A' listing every client (with additional columns showing their addresses) and another column 'H' showing specially selected clients from column 'A' without the addresses. The names are identical or nearly so.

    I need to identify which clients in column 'A' also exist in column 'H' so I can create a mail merge of just those clients.

    I'm in over my head.
    Last edited by jcoshow; 06-25-2011 at 07:48 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

    Re: compare cell to a range

    IMO a sample workbook would help in this situation!
    HTH
    Regards, Jeff

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,332

    Re: compare cell to a range

    What does
    The names are identical or nearly so
    mean?

    If it is identical, then you could use COUNTIF or VLOOKUP to set a "mail merge select" flag. If it's not identical, it would be more complicated ... but maybe still possible ... but you need to explain and give examples.

    Regards

  4. #4
    Registered User
    Join Date
    06-25-2011
    Location
    Portland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: compare cell to a range

    By '"nearly so" I meant there may be some slight differences like a missing period after an initial.

    I have attached a brief example from the real file. Column H are the clients I need for the final mail merge, column A is the listing of all clients.

    Jim
    Attached Files Attached Files

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

    Re: compare cell to a range

    In I1 you could use the Vlookup function, but instead of using a 0 at the end for an exact match, leave it blank which results in the closet match.

    Also, list should be in ascending order

    =VLOOKUP(H1,$A$1:$F$9,2)

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,332

    Re: compare cell to a range

    If it's just a missing period on one side or the other, you could use the formula:

    =IF(SUMPRODUCT(--(SUBSTITUTE($H$1:$H$10,".","")=SUBSTITUTE($A1,".","")))>0,"print","")

    to set a "flag".

    If it's other variations, you will need to extend the substitutions, etc.

    Regards

  7. #7
    Registered User
    Join Date
    06-25-2011
    Location
    Portland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: compare cell to a range

    I tried your formula but I didn't get the result I'm seeking. I didn't describe my goal well enough, let me try to clarify:

    In the updated attached example I would like to end up with a YES or NO on each row signifying if the name in column B also exists in column H. I will then sort out the NO's and complete the emerge on the remaining YES names.

    I have to run this merge regularly and it's become too big to do it manually.

    thanks in advance.

    Jim
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,332

    Re: compare cell to a range

    See amended example

    Regards

  9. #9
    Registered User
    Join Date
    06-25-2011
    Location
    Portland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: compare cell to a range

    Excellent TMShucks, thank you!
    Jim

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,332

    Re: compare cell to a range

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1