Hey guys,
Every week I run a report at my company. The report shows me all of the items on the floors of my stores. I need to find a way to compare the newest spreadsheet to the second most recent. I have to quickly be able to find any new additions and any drops. My secretary used to run an access program to do this, which I can do, but I would much rather not have to boot up in Windows if I can easily do this with Mac software. Any ideas?
iMac, Mac OS X (10.7.3)
Depending 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.
That 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.
A 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:
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
Comparing 2 spreadsheets
