
John De Banzie May 2, 2012 7:59 PM
Re: Comparing 2 spreadsheets in response to RLevitzDepending on your situation, this might work. It assumes that you have two tables, one with the new data and one with the old data, and that these two tables are the same size.
Create a third table the same size as the other two. In it enter a formula in every cell that compares the value of corresponding cells in "old" and "new". If the cells differ, have the formula enter "1", if not, have it enter "0". Conditionally format the cells in this table so that cells with "1" have a red fill.
Now put the "new" table on top of the third table that you have just created and adjust the transparency so that the red colour shows through.
Now any cell that has changed in the new table will be highlighted in red.
I got this idea from another thread in this forum. Unfortunately I don't remember who came up with it so that I can give credit. My apologies.

RLevitz May 2, 2012 9:24 PM
Re: Comparing 2 spreadsheets in response to John De BanzieThat is a good idea. Unfortunately, it won't work for me. The table size changes as different products are added and dropped too it. The table is also sorted alphabetically, so if the 10th item on the list is dropped, all of the rest of the items won't line up in between the tables. I need some script or something that will, for instance, take the value of cell A1 in table B and scan the entire column A of table A to see if that value exists. If it does, move on to A2. If it does not, highlight the value on Table B. This will find any new additions.
The inverse of this will also have to be done; scan every A cell of table A with reference to table B. This will find any dropped and discontinued products.
It's pretty much just a standard cross reference.

Barry May 3, 2012 1:44 AM
Re: Comparing 2 spreadsheets in response to RLevitzA COUNTIF() formula on each of the tables should do the job. You will be able to highlight the cells containing the formula, not the one containing the original item name. Here's a simple example.
The table on the left is named "Then", the one on the right is named "Now". The formulas in each table, entered in cell B2, then Filled down the rest of the column, are identical, except for the Table name in the cell reference to column A of the other table:
Then, B2: =COUNTIF(Now :: $A,A2)
Now, B2: =COUNTIF(Then :: $A,A2)
The conditional formatting rule for column B sets the same condition, but a different colour fill. Conditional formatting rules are set through the (Cell Format) Inspector.
Here's a second example, with a more complex formula that provides two enhancements:
 Note that the selected cell (B16) is no longer highlighted. This is handled by the first IF statement, which tests ( LEN(A)>0 ) for an entry in column A, and inserts three spaces into its cell if no entry is found.
 If an entry is found, the formula counts the occurrences of that entry in the other table. If the count is zero, the formula copies the item name from column A into its cell in column B; if the count is greater than zero (ie. if it is 1, assuming no double entries), the formula places a string of three spaces in its cell.
The revised formula looks for three consecutive spaces in the cell. If these are not found, the rule applies the selected highlight colour.
The two tables could be constructed each week, but it would be simpler to save the document as a template, then each week paste the item names from the previous week into the "Then" table and the new list into the "Now" table.
Regards,
Barry
 Note that the selected cell (B16) is no longer highlighted. This is handled by the first IF statement, which tests ( LEN(A)>0 ) for an entry in column A, and inserts three spaces into its cell if no entry is found.