# Compare data in two excel WORKBOOKS and highlight difference



## Jeddles_ (Jun 29, 2012)

Hello, 

As per the title I want to compare the data between two excel files not work sheets in the same file (if that males a difference). 

So I want to compare the old data with the new data and highlight the differences in red or any color on the new sheet. 

Thanks!


----------



## etaf (Oct 2, 2003)

youu cannot use conditional format on external workbooks to highlight the difference - how many columns do you want to compare and how many sheets 
some dummy data in a spreadsheet attached here would be good
you could set up a sheet that would compare the two sheets and then use conditional formatting

so

on a new sheet - sheet2 - cell A1
=IF('[A.xls]Sheet1'!A1='[B.xls]Sheet1'!A1, 0, 1)
then use a conditional format on the actual sheet 
Sheet2!A1 = 1 - make it red

That assumes both the files are open - you can use the full path if it is closed

so that would look like
='C:\abc\def\ghi\[A.xlsx]Sheet1'!A1
or
='C:\abc\def\ghi\[A.xls]Sheet1'!A1


----------



## Jeddles_ (Jun 29, 2012)

Hello it's from column A to AA.

File names are 
VCA v 5 GHPL BNE 020507.xls (old file)
VCA v 5 GHPL BNE 030507.xls (new file)

The files both have 1 tab with the same name (Vessel Clearance Advice).

So the formula Im trying to use is.
=IF('[VCA v 5 GHPL BNE 030507.xls.xls]Sheet1'!A1='[VCA v 5 GHPL BNE 020507.xls.xls]Sheet1'!A1, 0, 1)

Right?

Both the files will be open.


----------



## Jeddles_ (Jun 29, 2012)

Also 300 rows if that makes a difference


----------



## etaf (Oct 2, 2003)

back up the files - in case you make an error

so its the new data we want to test

Assumning above

on a new sheet in the new spreadsheet VCA v 5 GHPL BNE 030507.xls (new file)
call the sheet compare or something

so in cell A1 
=IF('[VCA v 5 GHPL BNE 030507.xls]Vessel Clearance Advice'!A1='[VCA v 5 GHPL BNE 020507.xls]Vessel Clearance Advice'!A1, 0, 1)
then copy the cell from A1 to AA1 using the bottom right corner of the cell - when mouse turns into a cross - do you know that method - if not just copy A1 and then highlight B1-AA1 and paste

now highlight A1 to AA1 - you can use the cross method and copy down to row 300 OR
copy
and then highlight A1 - AA1
and highlight A2-AA300 and paste

Now a 1 should appear where there is a difference

Now on the Vessel Clearance Advice sheet in VCA v 5 GHPL BNE 030507.xls (new file)
click on cell A1 and do a conditional format

and set up the formula
='compare'!A1=1

now the copy will be a problem if you have special formats anywhere 
because i want to then click on A1 in Vessel Clearance Advice sheet
and copy the conditional format to all the other cells 
so click on A1 and then copy and then Paste - *special>formats*
to AA300

I use paste special >- formats to only copy the format across the sheet - but that will also copy any format in A1 like bold, percent, number of decimal places

otherwise a macro is needed to do the lot


----------



## Jeddles_ (Jun 29, 2012)

this is what im using

=IF('C:\Documents and Settings\dicksj\Desktop\LAREE\[VCA v 5 GHPL BNE 030507.xls]Vessel Clearance Advice'!A4='C:\Documents and Settings\dicksj\Desktop\LAREE\[VCA v 5 GHPL BNE 020507.xls]Vessel Clearance Advice'!A4, 0, 1)

when i use this code it still wants me to show the file path.

So I create a new sheet, I copy that code throughout and it gives me the 0's and 1's. 
Then I don't know how to use conditional format...

When I try to set it up how you've said i get an error "you cannot use references to other worksheets or workbooks for conditional formatting criteria"


----------



## etaf (Oct 2, 2003)

will need a macro - hopefully someone will be able to look at that - I'm very rusty, but will have a look


----------



## etaf (Oct 2, 2003)

i have had a go at creating a macro to do this

Sheet1 is the sheet to add the colour to - if you are using a different name - i think you are using 
Vessel Clearance Advice

so change the macro below - so that sheet1 = Vessel Clearance Advice
and sheet2 = the name of the sheet with the 1's now on


```
Sub ColorCells()
For Each Cell In Sheets("sheet2").UsedRange
r = Cell.Row
c = Cell.Column
If Cell.Value = 1 Then Sheets("Sheet1").Cells(r, c).Interior.ColorIndex = 3
Next
End Sub
```
Install the Macro
copy the macro

Press "Alt + F11" - This will open the Visual Basic Editor

>left side where the vertical pane
>find your Excel file
>VBAProject {excel file name} > click on this

If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

copy the macro into the right hand area
close the Visual Basic Editor window
save the Excel file.

now you can run the macro

I have uploaded an example spreadsheet


----------



## Garf13LD (Apr 17, 2012)

In the helper column, use any lookup functions (Vlookup, match, or countif) to get a value
so say helper column is column B and data in column A.
in column A, enter conditional format as =B1=0 (0 means not found)


----------



## Jeddles_ (Jun 29, 2012)

Hey as far as I can tell it works...
I set the macro the open the new sheet sate the code in all the rows then it uses your above code to colour in the areas in the main sheet...

Just one thing, I think the code modified itself where as I was previously including both file names it now only needs the oldest file name. 
These files change everyday (as per ther current days date) I.e
VCA v 5 GHPL BNE 050712.xls
VCA v 5 GHPL BNE 060712.xls
VCA v 5 GHPL BNE 070712.xls

Is there anyway I could possibly tell the macro to point to the last days file? Or would that be something I'd need to change everyday?


----------



## etaf (Oct 2, 2003)

so you want to add the macro so that it apples to all excel files 
which we can do by using a personal.xls - 
and the name of the sheets does not change with the date - correct

so its the if statement that needs to have the name changed to work with a filename which has a date included and changes each day - but that would be better to add to a macro - so it all just runs each day 

so todays date is 11th July 2012
What would the dates be in the files - you want to use and compare
and then confirm the name on the sheets - do NOT have a date and are the same every day

do you have any control over the file names at all ?


----------

