Compare Sheets (Office 97 SR2)

Topic

New Reply

I have two almost identical spreadsheets, the only difference is in two of the colums, is there any way I can select just the rows which include the differences,

Reply | Quote

Replies

It’s not clear what you want to do about the differences. If you want to compare them, search here in the Excel Forum on ‘compare’ AND ‘cells’.

Reply | Quote

I you want to select the whole row where there are differences, then that will probably take a macro and we would need some additional information. Are these two worksheets in the same workbook or in different workbooks?. What is the column or columns where you want to check for differences. What are the worksheet names? On which workbook do you want to select the rows, or do you want to select them on both worksheets?

If you just want to select a cell on the row where there is a difference, then you can enter a formula like the one below in an empty column on one of the sheets:

=IF(A1Sheet2!A1,NA(),"")

then fill that formual down as far as there are rows to check. That should put a #NA in that column in each row where there are differences. The select GoTo from the Edit menu and click on the Special button in the dialog box. In this dialog box, click on Formulas, and then unselect everything by “Errors” and click on OK. That should select all of the cells with the #NA in them.

Reply | Quote

Hi Legare thanks for your response
These are two seperate workbooks that are price lists
But for some reason the italians can’t tell us which prices have increased!!! there for I have to compare the old price list with the new
Please see attachment

Reply | Quote

Since I don’t know if you have PKZIP, and I Can’t attach two files to the same message, I am attaching one workbook to this message and a second to a reply to this message that show how I would do this. I had to convert the Lira Gross column from text to numeric values.

Reply | Quote

Here is the second file.

Reply | Quote

Hi Legare

It worked fine, Once again thank you very much.
Braddy60

Reply | Quote

I use a similar technique from time to time:
0) Right-click toolbars, Customize, Commands (tab), Edit (Categories). The next-to-last button in the Commands list is Select Visible Cells. Add that button to a toobar of your choice.
1) Apply the formula, as Legare describes.
2) Apply AutoFilter (Data menu, Filter, AutoFilter).
3) In the column that contains the formula, filter on #N/A.
4) Select the range you want (for example, if you don’t want to select whole rows) – At this point, even the hidden rows are selected.
5) Click the Select Visible Cells button
6) Copy and paste them wherever you want, or do whatever else you want to/with them.

Reply | Quote