Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    comparing two worksheets (officexp 2002)

    i am trying to compare two work sheets. the master one being "A.xls" and the worksheet in file "B.xls" being anything but sequential. and contains lines which are not required.
    what i would like to do is compare the two files and delete any rows which do not match any of the cells in the column in file "A.xls"
    any one any ideas on where to start or pointers to an example on the web which might help

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing two worksheets (officexp 2002)

    John Walkenbach has a utility that contains a worksheet/workbook comparison tool.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing two worksheets (officexp 2002)

    thank you for your quick response, unfortunately this is not what i am looking for. see xls file attached.
    i am using sheet 2 as a master and would like to be able to delete all rows in sheet 1 which don't appear in sheet 2 whilst i have found an example of comparing 2 sheets i am having trouble in getting to the stage of creating a single column of figures which dose not reference the position in sheet 2

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: comparing two worksheets (officexp 2002)

    Hi alexander,

    In sheet 1 you could put the following formula in cell D5:
    =IF(COUNTIF(A$5:A$714,Sheet2!A3)=0,"!","")
    and copy all the way down. This puts a '!' in any cell in column D for which the corresponding value isn't found on sheet 2. Now select column D and do a PasteSpecial|Values, to 'harden' the results. Then all you need to do is to sort sheet 1 on column D, select all rows with the '!' and delete them.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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