Comparing cells between two files?

[newguy86]

Hello,

Here is the situation, I have two separate Excel files that have the same information in them from two sources. What I want to do is compare a cell in one file to the same cell in the other file and if the values of both cells equal each other then it moves to the next cell. If not it highlights the cell in one file and then moves on. I am wanting to do this so that I can easily maintain the information in these two sources and locate any inaccuracies. I have looked into Conditional Formating but it seems to only work with one file, not two.

I am considering creating a macro for this but I assume that this would be a simple enough task that Excel would have already built something for this.

Any suggestions would be greatly appreciated.

Travis

http://www.enterprise.com

 

[SkipVought]

Hi,

Perhaps a better description of the workbook, sheet.

Is the sheet a TABLE of data?

Can you post an example of data that's on both sheets that matches and does not and the exact result you would want to see from just that data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

Yes it is a table of data.

Example of the data:
Location Code Version # 1 Version # 2 Version # 3
xxxx01 01234 04321 09731
xxxx02 01234 00001 05001

Now both Excel files are supposed to be exactly the same, they are just coming from two sources and I can get the information onto two different Excel files but there are over 5000 Location Codes and I don't want to manually look through those offices twice. So all I need to do is have something that will basically say, start in cell A2 of each file. If the value of cell A2 in file 1 is equal to the value of cell A2 in file 2 then go to the next cell. Otherwise, change the color of the cell in file 1 to red, then go to the next cell.

Travis

http://www.enterprise.com

 

[SkipVought]

Rename the Headinsgs in BOTH workbooks, Version1, Version2, etc.

In Workbook 2, use Insert > Name > Create names in TOP row to name the data column ranges.

Copy the SHEET from Workbook2 to Workbook1.

On the Workbook1 original sheet, select the entire DATA AREA, starting with B2.

Select Format > Conditional Formatting

Select Formula Is: in the drop down

Paste in the Formula Textbox
[tt]
=INDEX(INDIRECT(B$1),MATCH($A2,Location_Code,0),1)<>B2
[/tt]
Select the RED pattern format.

Here's what this does...

You're using the Named Ranges from the Copied Sheet from Workbook 2.
INDIRECT always get the value in row1, which is the name in the Named Range.

INDEX, uses that name to return a value in that Named Range.

MATCH looks up the Location Code in that row in the Location_Code range, and returns the row offset for the INDEX function to use.

When the INDEX returned value is not equal to the value on the sheet, the FORMAT displays.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

I am guessing that when you say

"Copy the SHEET from Workbook2 to Workbook1."

you mean on the same sheet as Workbook 1. But is there a way to do that and have Workbook 2 copied onto another sheet inside Workbook 1?

I am asking because once I am finished with the whole process I want to print this up and give it as a report showing what needs to be fixed. So having all of this information on the same sheet twice will be a massive waste of time.

Travis

http://www.enterprise.com

 

[SkipVought]

I did not say to copy the CELLS on the SHEET. I DID say to copy the SHEET.

So if you had ONE sheet in wb1, and copies the SHEET from wb2 to wb1, now you would have TWO sheets in wb1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[newguy86]

Well I did as you said SkipVought and now its highlighting some of the columns that do have the correct information in them. It looks like it is offset to the left by one column. any suggestions?

Travis

http://www.enterprise.com

 

[newguy86]

To clarify what I mean, by the looks of it the sheet shows that column E is supposed to have what column F actually has.

Travis

http://www.enterprise.com

 

[newguy86]

Disregard my last messages, I was messing with it and got it to work.

Travis

http://www.enterprise.com