Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro or tool to compare two Excel spreadsheets

    Hi,

    I was very impressed with the solutions suggested in this site, I came across this site just today. I need help. I have to compare 100 reports in excel format. The same report will be generated from 2 different environments and I need to compare both the versions to see if they are same or have any difference. I got a VBA macro from a friend which does the comparison but it doesn't solve all my needs.

    That macro does line by line comparison but im expecting the 2 reports will not have the data in the same order. Line 1 in report1 could be line 3 in report2 so I need a macro to do keyword based comparison.here is what I need, pls suggest if you know or have a macro for this purpose.

    1. Compare all the files within a folder with the files in another folder
    2. identify the record and then compare
    3. update the results in the last column of the source file.

    Thanks,
    Prasanna

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    Surely there is a better solution than comparing spreadsheets. Can the reports be done another way, possibly fed into a simple database?

    Are you able to give us a couple of sample sheets to work with?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Oct 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    I'm not sure if feeding the excel data in to a database will work because all the files are not in the same format, the 2 files we are trying to compare are of the same format but every set is in a different format. I have attached 2 sample formats. I don't even know how many different layouts are there. If both the source and target files has the same data in the same rows then line by line comparison will work. My concern is what if there is an extra row in one of the files, I need the record based comparison in this case. Not sure what is the best way. Thanks for taking time to help me.

    Thanks,
    Prasanna
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    I can't see any correlation between those two sheets. Do you have 2 sheets that you would be comparing?

    cheers, Paul

  5. #5
    New Lounger
    Join Date
    Oct 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Sorry for the confusion, Paul. Attached 2 sample files to be compared. Thanks!
    Attached Files Attached Files

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    That's a real apple n oranges report. There are several things to compare to work out if a value is different, unless you just count number of items.
    What do you use to decide if an item is the same? Section A may use Group ID and Subgroup ID, Section B Group ID , Subgroup ID and Subscriber ID?
    What do you update if you find a discrepancy?

    cheers, Paul

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    prasannacts,

    in addition to the questions Paul raises, please answer these:

    Will the naming conventions be consistent as above between the files in one folder as compared to it's sister file in the other folder?

    Must all the cells in the record be identical to be considered a match or are there certain columns that may be considered insignificant in the comparison?

    How do you want to handle the discrepancies? This could be as simple as placing an "X" in column L on both sheets next to the columns that have a match or could be as complicated as creating a separate sheet with all the unique listings that match and all the non matching records.

    Maud

  8. #8
    New Lounger
    Join Date
    Oct 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul and Maud,

    To identify the unique records, I will use group id, group name and sub group id in section A. In section B, group id and subscriber id can be used to find the unique records.

    The file name will be exactly same for both the source and target files.

    All the cells in the record should be identical to be considered a match.

    For each row in both the files, I want the last column to be updated to indicate the result, either match or no match. No need to have a separate sheet.

    Thanks again for taking your time to respond.

    Prasanna

  9. #9
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    In the attached file, the macro requests a filename, opens the two files and does a comparison. To open the two samples you sent, just use "002_Daily" as the filename when requested. The macro appends the two suffixes for you.

    It needs to be in the same folder as the files.

    Hope this helps.
    Attached Files Attached Files

  10. The Following User Says Thank You to unclehewie For This Useful Post:

    prasannacts (2015-10-21)

  11. #10
    New Lounger
    Join Date
    Oct 2015
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    unclehewie,

    Thanks for the macro, I tried it. It compares only 3 columns but for each record I need all the cells in the record should match between source and target. sorry if my previous posts were confusing. There are at least 200 reports I need to compare, all the reports are not in the same format, the number of columns could be different in each report.

    for example, if a file has 100 rows and 5 columns, the macro should take row 1 from source file, search for that row through out the target file (the record might not be in the same row in both the files), if a match is found then update the last blank column in both the files as "match found" for that record. then take row 2 from source file and do the same process, then row 3 and so on until the last row. The last row will always be ***End of Report***. If there is no match found in the target file, then update "no match" for that record in the source file.

    I have attached a macro I'm using currently. It does line by line comparison and also compares multiple files one by one. I need the following changes to be done in this macro, if you could help me, that will be great.

    1. instead of comparing row 1 from source with row 1 from target, search for the record through out the target file because the same record will be in different rows in source and target.
    2. currently the macro copies the source and target files and put them in a new file, 2 sheets within the same file, one for source and another for target, this is great. It highlights the differences in some color in the source sheet names "first1". Apart from highlighting the differences, I also need the last column in the "first1" and "second1" sheets to be updated with match or no match as I explained in the above statement.

    Thanks again for your help.

    Prasanna
    Attached Files Attached Files

  12. #11
    jwoods
    Guest
    You might try downloading and using the trial version of Beyond Compare...

    http://www.scootersoftware.com/

  13. #12
    New Lounger
    Join Date
    Oct 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can do in Corel

    OK, this is probably going to draw some comments.

    Open your Excel files in Corel WordPerfect. WordPerfect recognizes the spreadsheet as a table. Save in .wpd.

    Then use the built-in redline feature in WordPerfect to generate your redline.

    See attached. I compared the monthly to the daily. Easy peasy. The best built-in redline capacity is in WordPerfect.

    Now, these are two radically different spreadsheets. Send me two files that you really want in redline and I'll do it again.

    Sincerely,

    Randy K.
    Fresno, CA

    Quote Originally Posted by prasannacts View Post
    Hi Paul,

    I'm not sure if feeding the excel data in to a database will work because all the files are not in the same format, the 2 files we are trying to compare are of the same format but every set is in a different format. I have attached 2 sample formats. I don't even know how many different layouts are there. If both the source and target files has the same data in the same rows then line by line comparison will work. My concern is what if there is an extra row in one of the files, I need the record based comparison in this case. Not sure what is the best way. Thanks for taking time to help me.

    Thanks,
    Prasanna
    Attached Files Attached Files

  14. #13
    New Lounger
    Join Date
    Feb 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I opened CompareMacros.xls and enabled the Macro, all spreadsheets were empty.

  15. #14
    New Lounger
    Join Date
    Oct 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jack - My output was generated in pdf. Redline file was uploaded as Excel compare.pdf.

  16. #15
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    I've amended your file so that it now does what I think you want it to do.

    (I've added Option Explicit in the code as I like working that way, but you may want to remove that.)
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •