I have the following scenario:
- I have a spreadsheet A with a large table.
- I open spreadsheet B and copy the table from A into it.
- I want to check if I copied the table from A to B correctly.
In order to do step 3 I usually sum (and average, etc.) all the numbers in the table check if I get the same number as I get for the original table.
However, I would like to have a checksum function like checksum(<2D RANGE>) which would return a checksum (e.g. MD5) of the table in the given range. I would use this checkusum function in spreadsheet A and then in spreadsheet B and compare the obtained values to find out if the table I copied matches the original one.
1 Answer
You could piece together something with worksheet functions that pairwise compares the values in your source and target tables and then returns TRUE if the values match and FALSE if not.
For example, the following compares A1:Z100 on Sheet1 and Sheet2.
=SUMPRODUCT(1*EXACT(Sheet1!A1:Z100,Sheet2!A1:Z100))=ROWS(A1:Z100)*COLUMNS(A1:Z100)This does a pairwise case-sensitive comparison of the cells in each range and counts the number of matches. This count is then compared to the number of cells in the rectangular range. If the counts match, then TRUE. Otherwise, FALSE.
Note that this only compares the values in the cells. This does not check formulas, only their output.
