Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Sort / Filter (2002 / 10)

    I have two spreadsheets, one from 2007 and one for 2008. The information contained are customers who have contracted with our company for annual lawn care. My boss would like to send reminder postcards to those who have not returned their contracts for 2008. How would I sort out from the 2007 file the names that are not on the 2008 file? Is this possible?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort / Filter (2002 / 10)

    You can use a MATCH to look at some unique identifier in one list to the other. If you do not have a "cutomer ID", you may have to combine some columns together as an intermediate then use a MATCH on that column.

    If you provided a couple sample spreadsheets (can be with fake data but your setup) we could provide a more detailed answer...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort / Filter (2002 / 10)

    Let's say the customer names are in column A, starting in A2.

    Open both workbooks.
    Activate the one for 2007.
    In the first empty column, enter the word Missing in row 1.
    In row 2, start entering the following formula:

    =ISERROR(MATCH(A2,

    Then (while still in formula edit mode), activate the 2008 worksheet and select the range with names.
    You'll see something like

    =ISERROR(MATCH(A2,[Data2008.xls]Sheet1!$A$2:$A$100

    Complete the formula by typing ,0)) and pressing Enter. You should see something like the following in the formula bar:

    =ISERROR(MATCH(A2,[Data2008.xls]Sheet1!$A$2:$A$100,0))

    Fill down as far as needed. You can use AutoFilter to select only the TRUE values in this column.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts

    Re: Sort / Filter (2002 / 10)

    Again you have come through for me!! Thank you, thank you, thank you!!! My boss was going to sit down with hard copies of both files and visually compare them to find the "missing" 2008 customers. I told him I KNEW there was an easier and better way (as you can guess, he's computer illiterate, but it's because he's visually impaired, which makes it even worse that he was going to compare them that way!) I keep all the "answers" from Woody's Lounge for future reference; I learn more each time I come here. Thanks again!

Posting Permissions

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