Comparing two spreadsheet.

[Kerri]

I really want to see the differences in two spreadsheets.
Any ideas how I can get excel to highlight the differences
in one?

 

[Myrna Larson]

Let's say the used range is A1:J200 on each sheet. You could set up a helper area, say K1:T200,
and in K1 put the formula

=Sheet1!A1<>Sheet2!A1

Copy to fill the area K1:T200.

Then you can set up conditional formatting on A1:T200 to color the cell if (for cell A1),
K1=TRUE.

Or you could write a VBA macro that compares the sheets and colors the mismatched cells.

 

[Gilles Desjardins]

Hi Myrna,

I am using your "Compare" XLA. Great stuff thank you. Can it be modified to
show me the cells that are the same?
OR
Is there a way, with a formula, to do what I ask?

TIA

Gilles Desjardins

 

[Myrna Larson]

Can it be modified to show me the cells that are the same?
With a lot of work, yes. But that defeats the whole purpose.

I suggest you set up formulas that compare the cells, then use conditional formatting as I said
in the previous message. If you want to see the cells that are the same, use the conditional
formatting to specify TRUE or FALSE in the helper column, depending on how you wrote the formula
there.

 

[Gilles Desjardins]

Thank you for the prompt reply.

Gilles Desjardins