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.

Macros for comparing two excel files

Discussion in 'Business Applications' started by xpower, Feb 11, 2009.

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

    xpower Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    6
    Hello,

    I am new to macros. I want to compare two excel spreadsheets. Specifically, Book1 is the source file with upto 4 columns populated. Book2 has only one column populated with data that might be contained in any of the 4 columns of Book1. What I am looking for this, a macro to compare each cell of Book2 with the contents of Book1 and if there is a match, to highlight the particular cell in Book1 and in Book2.

    I would appreciate any help.

    Thank you in advance for your help.
     
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Ok, first to get things clear, you talk about separate spreadsheets. Are you talking about two different workbooks - two entirely different files, that is - or two worksheets in the same workbook?
    The latter is rather simple and can be done via a formula, at least the matching. Changing the color in both books could be done via some more formulas and conditional formatting, although VBA is probably a better way to do it.

    The first case requires that both workbooks be open at the same time - something that VBA can call for.
     
  3. xpower

    xpower Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    6
    In my case, it is two different workbooks - two entirely different files.

    If the case of two worksheets in the same workbook is simpler, I could do that too, it is simply a matter of pasting the contents into one workbook.

    I apreciate your help.
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Just curious, could the data in a cell in Book 2 ever be in more than one place in Book 1?
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hey turbo - I was just coming to see if you wanted to join in here! ;)
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    An interesting bit of coding here
    http://www.thecodenet.com/downloads.php
    It doesn't deal with multiple workbooks - but I think that isn't terribly complex, just instruct the file to be opened at the start of your code - but look for Excel Search/Find Tutorial by Kid Van Ouytsel.
     
  7. xpower

    xpower Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    6
    You asked: "Just curious, could the data in a cell in Book 2 ever be in more than one place in Book 1?" - For a given cell value, the entire text may not appear more than once, but a part of it may appear more than once.

    Thanks for the site. I tried the code for "A flexible search for multiple items." But I am running into an error here: For Each Fr In Range("Flex") Should I be defining "Flex"? Also, does it matter if I have only 'Sub' instead of 'Private Sub'?

    I have pasted the code here for your convenience. Thanks for your help
    ----------------------------------------------------------------------
    '"A flexible search for multiple items" example.
    Private Sub FlexiSearch_Click()
    Dim R As Range, Fr As Range
    Dim FindAddress As String

    'Reset the interior color of the range to none.
    Range("M10:O22").Interior.ColorIndex = xlNone

    'Loop through each cell of the named range "Flex".
    For Each Fr In Range("Flex")
    'If the cell is not empty then.
    If Fr.Value <> "" Then
    With Range("M10:O22")
    'Start searching for the cell value in Range("M10:O22").
    Set R = .Find(Fr.Value)
    'If a match is found.
    If Not R Is Nothing Then
    'Store the address of the cell where the first match is found in a variable.
    FindAddress = R.Address
    'Start to loop.
    Do
    'Color the cell where a match is found grey
    R.Interior.ColorIndex = 15
    'Search the next cell with a matching value.
    Set R = .FindNext(R)
    'Loop as long matches are found, and the address of the cell where a match is found,
    'is <> as the address of the cell where the first match is found (FindAddress).
    Loop While Not R Is Nothing And R.Address <> FindAddress
    End If
    End With
    End If
    'Go to the next cell.
    Next Fr

    'Clear memory.
    Set R = Nothing

    End Sub
     
  8. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    So would you only be looking for exact matches or partial matches as well?
    Yes, "Flex" appears to be a defined range (I am concluding this just from the code - I haven't looked at the file since I got home) .
     
  9. xpower

    xpower Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    6
    Yes I would be looking for exact matches and partial matches.

    I will try defining "Flex" and will let you know the result.

    Thank you.
     
  10. xpower

    xpower Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    6
    I tried defining "Flex" as a range, but it didn't work.

    Also I am using the 'Sub' instead of 'Private Sub' as in the code. This is because, when I add Private, the macro doesn't show up anymore. May be you can help me there. Thanks.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Not me - I can barely track easy VBA.
    But I will see if there is anyone floating around who does....
     
  12. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    I need a batsign and a beacon :p
     
  13. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    How have you defined it? I just tested the code, and it seems to do the job for a small range okay.

    In place of "Flex", I used the range "I10:K22" ie.

    For Each Fr In Range("I10:K22")


    This bit of code called "Private Sub FlexiSearch_Click()" is normally executed from a userform click button. If you want to run it as a garden variety macro, then just change it to something like "Sub FlexiSearch"
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Thanks for coming, turbo! :)
     
  15. xpower

    xpower Thread Starter

    Joined:
    Feb 11, 2009
    Messages:
    6
    It works fine now!

    Just wondering, what should be changed, if the data is in two different workbooks - two entirely different files?

    I really appreciate your help. Thank you.
     
  16. 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...
Similar Threads - Macros comparing excel
  1. JAMESMAINE
    Replies:
    4
    Views:
    257
Thread Status:
Not open for further replies.

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

  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