1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Compare data in two excel WORKBOOKS and highlight difference

Discussion in 'Business Applications' started by Jeddles_, Jul 3, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Jeddles_

    Jeddles_ Thread Starter

    Joined:
    Jun 29, 2012
    Messages:
    69
    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!
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    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
     
  3. Jeddles_

    Jeddles_ Thread Starter

    Joined:
    Jun 29, 2012
    Messages:
    69
    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.
     
  4. Jeddles_

    Jeddles_ Thread Starter

    Joined:
    Jun 29, 2012
    Messages:
    69
    Also 300 rows if that makes a difference
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    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
     
  6. Jeddles_

    Jeddles_ Thread Starter

    Joined:
    Jun 29, 2012
    Messages:
    69
    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"
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    will need a macro - hopefully someone will be able to look at that - I'm very rusty, but will have a look
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    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


    Code:
    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
     

    Attached Files:

  9. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    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)
     

    Attached Files:

  10. Jeddles_

    Jeddles_ Thread Starter

    Joined:
    Jun 29, 2012
    Messages:
    69
    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?
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,856
    First Name:
    Wayne
    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 ?
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1059587

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice