comparing two worksheets (officexp 2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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

Reply | Quote