Compare data in 2 workbooks and highlight differences in red
 

Using: Windows XP/Excel 2003 - What I'm trying to do is compare two
inspection forms (that should be exactly the same) to see if anyone has
changed any of the questions on the forms. For example: In the "master form"
a specific cell asks the question for the "sq. ft. of a building" and in the
"copy form" I want to compare, if someone has changed that question to ask
the "qty of units in the building". I want to compare that each cell has
exactly the same data in both of the the forms and if different then to
highlight the cells with differences in red on the "copy form". What I have
to accomplish is that I have 200+ forms that I have to compare to a single
master form to find which cells are different so that I can have them mapped
to our database. I do understand that i wil have to do them one by one, I
just know there is an easier way than manually comparing each individual cell
in 200+ forms, Yikes! I am somewhat new to this so please explain steps in
detail. I appreciate any help that you can give.
--
Thank you,
Sherry

Compare data in 2 workbooks and highlight differences in red
 

Depending on your layout, could do someething like this in a 3rd workbook

=IF(EXACT('[Book2]Sheet1'!$A$1,'[Book1]Sheet1'!$A$1),"",'[Book2]Sheet1'!$A$1)
Where Book2 is the copied data. Copy this cell over a wide area in your 3rd
workbook (say, A1:Z200)
Anytext that is displayed is where there is a discrepency between the two
books.

For other ideas, you could try using conditional formatting, or the MATCH
function (see XL help, or peruse this newsgroup)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sherry" wrote:

Using: Windows XP/Excel 2003 - What I'm trying to do is compare two
inspection forms (that should be exactly the same) to see if anyone has
changed any of the questions on the forms. For example: In the "master form"
a specific cell asks the question for the "sq. ft. of a building" and in the
"copy form" I want to compare, if someone has changed that question to ask
the "qty of units in the building". I want to compare that each cell has
exactly the same data in both of the the forms and if different then to
highlight the cells with differences in red on the "copy form". What I have
to accomplish is that I have 200+ forms that I have to compare to a single
master form to find which cells are different so that I can have them mapped
to our database. I do understand that i wil have to do them one by one, I
just know there is an easier way than manually comparing each individual cell
in 200+ forms, Yikes! I am somewhat new to this so please explain steps in
detail. I appreciate any help that you can give.
--
Thank you,
Sherry

Compare data in 2 workbooks and highlight differences in red
 

Hi Luke, Thanks for the quick response. When I tried pasting it, the formula
just changed all on its own. And since I am sooo new to this, I don't
understand how to make it look at the 2 workbooks.
--
Thanks for all of your help,
Sherry



"Luke M" wrote:

Depending on your layout, could do someething like this in a 3rd workbook

=IF(EXACT('[Book2]Sheet1'!$A$1,'[Book1]Sheet1'!$A$1),"",'[Book2]Sheet1'!$A$1)
Where Book2 is the copied data. Copy this cell over a wide area in your 3rd
workbook (say, A1:Z200)
Anytext that is displayed is where there is a discrepency between the two
books.

For other ideas, you could try using conditional formatting, or the MATCH
function (see XL help, or peruse this newsgroup)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sherry" wrote:

Using: Windows XP/Excel 2003 - What I'm trying to do is compare two
inspection forms (that should be exactly the same) to see if anyone has
changed any of the questions on the forms. For example: In the "master form"
a specific cell asks the question for the "sq. ft. of a building" and in the
"copy form" I want to compare, if someone has changed that question to ask
the "qty of units in the building". I want to compare that each cell has
exactly the same data in both of the the forms and if different then to
highlight the cells with differences in red on the "copy form". What I have
to accomplish is that I have 200+ forms that I have to compare to a single
master form to find which cells are different so that I can have them mapped
to our database. I do understand that i wil have to do them one by one, I
just know there is an easier way than manually comparing each individual cell
in 200+ forms, Yikes! I am somewhat new to this so please explain steps in
detail. I appreciate any help that you can give.
--
Thank you,
Sherry

Compare data in 2 workbooks and highlight differences in red
 

OK, I got it figured out but, I can't get the formula to change to the cell
values that I copy it or drag it into, it keeps the same cell name in the
formula as the first formula ($A$1) and I have to change it manually to the
next (A2, A3, B1, B2 etc.). How do I get it to copy and automatically update
to the cell name over thousands of cells.

Here's my formula:

=IF(EXACT('[Wachovia Commercial Inspection Form.xls]Report'!$A$1,'[Bank of
America Standard Inspection Form 5-11-07.xls]Report'!$A$1),"OK","MISMATCH")


--
Thanks for all of your help,
Sherry



"Luke M" wrote:

Depending on your layout, could do someething like this in a 3rd workbook

=IF(EXACT('[Book2]Sheet1'!$A$1,'[Book1]Sheet1'!$A$1),"",'[Book2]Sheet1'!$A$1)
Where Book2 is the copied data. Copy this cell over a wide area in your 3rd
workbook (say, A1:Z200)
Anytext that is displayed is where there is a discrepency between the two
books.

For other ideas, you could try using conditional formatting, or the MATCH
function (see XL help, or peruse this newsgroup)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sherry" wrote:

Using: Windows XP/Excel 2003 - What I'm trying to do is compare two
inspection forms (that should be exactly the same) to see if anyone has
changed any of the questions on the forms. For example: In the "master form"
a specific cell asks the question for the "sq. ft. of a building" and in the
"copy form" I want to compare, if someone has changed that question to ask
the "qty of units in the building". I want to compare that each cell has
exactly the same data in both of the the forms and if different then to
highlight the cells with differences in red on the "copy form". What I have
to accomplish is that I have 200+ forms that I have to compare to a single
master form to find which cells are different so that I can have them mapped
to our database. I do understand that i wil have to do them one by one, I
just know there is an easier way than manually comparing each individual cell
in 200+ forms, Yikes! I am somewhat new to this so please explain steps in
detail. I appreciate any help that you can give.
--
Thank you,
Sherry

Compare data in 2 workbooks and highlight differences in red
 

Get rid of the $ signs in your first formula then start your copying across
and down.

See help on absolute and relative referencing for rows and columns.


Gord Dibben MS Excel MVP


On Mon, 12 Jan 2009 15:41:00 -0800, Sherry
wrote:

OK, I got it figured out but, I can't get the formula to change to the cell
values that I copy it or drag it into, it keeps the same cell name in the
formula as the first formula ($A$1) and I have to change it manually to the
next (A2, A3, B1, B2 etc.). How do I get it to copy and automatically update
to the cell name over thousands of cells.

Here's my formula:

=IF(EXACT('[Wachovia Commercial Inspection Form.xls]Report'!$A$1,'[Bank of
America Standard Inspection Form 5-11-07.xls]Report'!$A$1),"OK","MISMATCH")