Skip navigation

Comparing 2 spreadsheets

301 Views 3 Replies Latest reply: May 3, 2012 1:44 AM by Barry RSS
RLevitz Calculating status...
Currently Being Moderated
May 2, 2012 3:43 PM

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)
  • John De Banzie Level 2 Level 2 (430 points)
    Currently Being Moderated
    May 2, 2012 7:59 PM (in response to RLevitz)

    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.

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    May 3, 2012 1:44 AM (in response to RLevitz)

    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.

    Picture 39.pngThe 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:

    Picture 41.png

    1. 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.

    2. 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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.