none
Compare two excel spreadsheets RRS feed

  • Question

  • Hi,

    I am trying to compare two excel spreadsheets. There are 4 columns in each file

    Tel No                        Licence type        Partner         End user

    2031111111               business              xyz incp       abc ltd

    One sheet has been picked up from the CRM system and the other one from billing. I want to filter/find the tel no's not in billing by comparing the two files. There are nearly 20,000 records in each file.

    So, pick first telephone number from CRM file and look for the matching telephone number in the billing file if its there bingo if its not there colour code it by turning that cell to red. One number is matched against 20,000 records and for every single record the process is repeated.

    Later on I want to expand the selection by picking up Tel no and licence type and comparing it with the billing file.

    Hope that it makes sense.

    Your help will be greatly appreciated.

    Thanks

    Tuesday, June 16, 2015 3:57 PM

Answers

  • Hello,

    I see the following main ways to get the job done:

    1. Automate Excel to compare files (or just create VBA macro). See https://support.microsoft.com/en-us/kb/219151 for more information. If decide to develop a VBA macro, see Getting Started with VBA in Excel 2010 . 

    2. If you deal with open XML documents only (.xslx) you may consider using the Open XML SDK. See Welcome to the Open XML SDK 2.5 for Office to get started quickly. Due to the fact that you have 20000 records that way is preferred. 

    3. Use third-party components that don't require Excel installed on the PC. Try to search on the web for any of them. Don't like to advertise any there.

    • Marked as answer by L.HlModerator Sunday, June 28, 2015 2:27 PM
    Tuesday, June 16, 2015 8:56 PM
  • Compare sheets and highlight red if different?  please try this simple script.

    Sub Compare2Shts()
    For Each Cell In Worksheets("Sheet1").UsedRange
    If Cell.Value <> Worksheets("Sheet2").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next
    
    For Each Cell In Worksheets("Sheet2").UsedRange
    If Cell.Value <> Worksheets("Sheet1").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by L.HlModerator Sunday, June 28, 2015 2:27 PM
    Tuesday, June 16, 2015 10:44 PM

All replies

  • Hello,

    I see the following main ways to get the job done:

    1. Automate Excel to compare files (or just create VBA macro). See https://support.microsoft.com/en-us/kb/219151 for more information. If decide to develop a VBA macro, see Getting Started with VBA in Excel 2010 . 

    2. If you deal with open XML documents only (.xslx) you may consider using the Open XML SDK. See Welcome to the Open XML SDK 2.5 for Office to get started quickly. Due to the fact that you have 20000 records that way is preferred. 

    3. Use third-party components that don't require Excel installed on the PC. Try to search on the web for any of them. Don't like to advertise any there.

    • Marked as answer by L.HlModerator Sunday, June 28, 2015 2:27 PM
    Tuesday, June 16, 2015 8:56 PM
  • Compare sheets and highlight red if different?  please try this simple script.

    Sub Compare2Shts()
    For Each Cell In Worksheets("Sheet1").UsedRange
    If Cell.Value <> Worksheets("Sheet2").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next
    
    For Each Cell In Worksheets("Sheet2").UsedRange
    If Cell.Value <> Worksheets("Sheet1").Range(Cell.Address) Then
    Cell.Interior.ColorIndex = 3
    End If
    Next
    End Sub


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by L.HlModerator Sunday, June 28, 2015 2:27 PM
    Tuesday, June 16, 2015 10:44 PM