Compare Before Spreadsheet Data to After Spreadsheet

[Nettie_in_Nebraska]

Greetings...
First, let me say I am a 'business' person and I need to present the output of a 'technical' fix to another business person! :-)

Wondering if there is an easy way to compare 2 spreadsheets that should have identical data on them? The first spreadsheet (Before) has the output data from 'before' a code fix was applied. The second spreadsheet (After) has the output data from 'after' a code fix was applied. The spreadsheets have 7 columns of data and almost 500 rows.

I've already copied the data from the source datasets provided by my IT folks into Notepad (.txt) files and then used Excel to open them as fixed width spreadsheets. I have 1 workbook with 1 spreadsheet with 'before' data. And, I have 1 workbook with 1 spreadsheet 'after' data. And, I have another workbook that contains both worksheets. So, I'm ready to go whenever I get hints of what to do next. :-)

I need to be able to show my client that we did not impact the data with the code fix that was applied. I want to be able to show my client contacts (business folks) an end result via Excel that confirms that I actually compared the 2 sheets and there were no differences. In other words....I can't just show them a formula with '0' as it end result (even tho that's basically what I'm trying to prove). Any suggestions?

Many thanks in advance!
Nettie

[BadlySpelledBuoy]

Without sight of some dummy data it's difficult to provide a decent solution.

Also not sure about this bit, "I can't just show them a formula with '0' as it end result". What exactly do you want the outcome to be??

[Nettie_in_Nebraska]

Greetings!
I've attached a .jpeg that has the same data that I'm working with. Suffice it to say...both the before and after spreadsheets should be exactly the same.

And below is a better explanation of what I meant about the formula.
Before_Data.JPG

I used the following formula on a 3rd worksheet in the workbook: =IF('Compare Results'!A1=[Sheet2]Sheet2!A1,"","different")
and my result from that formula (in cell 1A) basically said 0 (zero).

My problem is I'm not sure my formula is correct and even if it is correct if the person I'm presenting it to will understand what I did. :-)

Does that clear up the ambiguity a bit?

Appreciate any assistance!
Nettie

[BadlySpelledBuoy]

In Sheet3, use =IF('Sheet 1'!A1='Sheet 2'!A1,"","Different") in cell A1 and copy down (amend Sheet 1 and Sheet 2 to your appropriate worksheet names) it should work.

As for the person you're presenting it to understanding what you've done, well it's your job to explain how you've done what you've done.

Personally I would use conditional formatting to highlight any cells that are different between the two worksheets, but this would still need to be explained to the person it's being presented to.

Perhaps I'm just missing the point in what you're trying to accomplish..

[newdoverman]

Here is a relatively easy way of finding differences between the two worksheets if they are in the same workbook.

On a third worksheet, enter this in A1 and copy across and down covering the number of rows and columns of the worksheets.

Please Login or Register  to view this content.

This will result in TRUE and FALSE in the third worksheet. FALSE means that the cells DO NOT MATCH.

	A	B	C	D	E	F
1	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
2	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3	TRUE	FALSE	TRUE	TRUE	FALSE	TRUE
4	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
5	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
6	TRUE	TRUE	TRUE	FALSE	TRUE	TRUE
7	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE

To further enhance finding the differences use Conditional Formatting to highlight the FALSE returns. Select the area of the third worksheet that corresponds to the area of the worksheets being compared. Click on Conditional Formatting, New, Use Formula.... and enter this formula in the formula field.

Please Login or Register  to view this content.

Choose the formatting that you want the FALSE cells to have. Click OK until out of Conditional Formatting.

[Nettie_in_Nebraska]

First, thank you for your assistance! I will try your formula.

As far as conditional formatting concerned, in my specific example there wouldn't be any highlighting if in fact the 2 sheets are identical (which is my coder's end goal), right? I've not used conditional formatting before so I'm not sure if there'd be anything highlighted since the 2 sheets should be exactly equal.

[newdoverman]

If nothing is highlighted, then both worksheets are identical. Isn't that what you want?