8 Replies Latest reply: Sep 19, 2014 8:51 AM by fib72
rodgerb4 Level 1 Level 1 (40 points)

Is there a way to compare two spreadsheets of the same database for discrepancies? I have two very la rge spreadsheets and one has a few more rows than the other and I need to find the difference between the two to correct the difference and make the the same by deleting the rows with the wrong information. There are around 5,000 rows in each database withe a difference of about 6 rows of data.


MacBook Pro, Mac OS X (10.7.2)
  • Level 8 Level 8 (41,790 points)

    If you are asking upon two tables in the same document, it's quite easy.

     

    Assuming that the table with the greater number of rows is named Table 1

    and that the other one is named  Table 2

    Assuming too that both use 5 columns insert 2 new columns on their right.

    In the first standard cell of the new column F of both tables, insert the formula :

    =A&B&C&D&E

    Apply fill down

    In Table 1, in the first standard cell of the new column G, insert the formula :

    =IF(ISERROR(VLOOKUP(F,Table 2 :: F,1,FALSE)),"mismatch","")

    Apply Fill Down

    In Table 2, in the first standard cell of the new column G, insert the formula :

    =IF(ISERROR(VLOOKUP(F,Table 1 :: F,1,FALSE)),"mismatch","")

    Apply Fill Down

     

    You will get the string mismatch in rows which haven't a clone in the other table.

     

    Yvan KOENIG (VALLAURIS, France) dimanche 22 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

    My iDisk is : http://public.me.com/koenigyvan

  • Wayne Contello Level 6 Level 6 (15,210 points)

    The tables are large so it can be slow.

     

    One way I like to use is to:

    1) copy the two tables into a new document

    2) make them both semi-transparent (by select the tabled then opening the "Graphic Inspector") and sliding the opacity to about 50%:

    Screen Shot 2012-01-22 at 9.29.05 AM.png

    Tables are now transparent:

    Screen Shot 2012-01-22 at 9.26.25 AM.png

     

    Then I move the tables one top of one another:

    Screen Shot 2012-01-22 at 9.27.00 AM.png

     

    This is a manual operation becuase you have to visually look but it is easy to spot and you don't get punish for having a large data set and adding additional formulas which will make things even slower.

  • rodgerb4 Level 1 Level 1 (40 points)

    Yvan, thanks for the reply. They are actually two different documents with the same information and they are in MS Excell and I will have to bring them into Mac numbers, I think I can email them from the PC and then drag them into numbers to convert them. but they are from two different databases on the same subject and I have just discovered that the problem si worse than I thought. there is only a difference of 6 rows but I have just found the some of the rows have differnent data in them for the same row number %*&$? What a mess. This is for a Municipal life safety system covering a very large City.

    Rodger

  • rodgerb4 Level 1 Level 1 (40 points)

    Hi Wayne, this just got a lot more complicated if you read my reply to Yvan. Thanks for the reply and it looks like I may have to do it your way and one at at time? I just can't afford to lose any of the right data, to much at stake.

    Rodger

  • Level 8 Level 8 (41,790 points)

    What's the problem ?

    My scheme doesn't compare row1 with the other row 1.

    It returns "mismatch" if there is no clone of a given row in the other table.

    A screenshot was available but the forum was out for maintenance whenn I tried to insert it.

    Here it is :

    2012-01-22T16.15.35.jpg

    I guess that there is a way to do the same in Excel.

     

    Yvan KOENIG (VALLAURIS, France) dimanche 22 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

    My iDisk is : http://public.me.com/koenigyvan

  • rodgerb4 Level 1 Level 1 (40 points)

    I don't know if there is a problem with your solution and I do appreciate the help. I was basically stating that they ae two seperate documents of the same information. You asked if they were the same document. They are the dame type of document but from two different database's with the same information but the amount of rows do not correspond with each other and I am trying to find the difference between the two?

    Rodger

  • Level 8 Level 8 (41,790 points)

    I understood well.

    As your tables are in two different documents, the first thing to do is to copy, say the second one, and paste it in the sheet of the document embedding the 1st one.

    This way, you will have the two different tables in a single sheet and you will be able to apply the described scheme.

    If you open your eyes, you will see that the two tables used in my example haven't the same number of rows.

    I apologize.

    I'm able to build a sample document and write the needed formulas but I'm unable to open your eyes.

    I repeat that it's designed to apply to tables whose rows may have different contents. Of course as long as column A of table 1 contains the same kind of infos that column A in  table 2

    And even this requirement is not an obsolute one.

    Look :

    2012-01-22T22.55.20.jpg

    The datas which were in column A of Table 1 are now in column D.

    I just have to change the formula used in column F

     

    It was =A&B&C&D&E

    It's now : =D&A&B&C&E

    I didn't changed the formulas in column G of Table 1, in columns F & G of table 2.

     

    Yvan KOENIG (VALLAURIS, France) dimanche 22 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • fib72 Level 1 Level 1 (0 points)

    Thanks for sharing this Yvan - exactly what I was looking for and worked a treat