Compare spreadsheets for discrepancies

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%:

Tables are now transparent:

Then I move the tables one top of one another:

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.

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 :

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

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 :

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

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

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

Just tried this in Numbers 3.5.3, replacing F with G, as my table had more columns, and adding the table name (when I imported as CSV, the tables were unnamed by default). Worked great. Thanks!