+ Reply to Thread
Results 1 to 18 of 18

find differences between 2 files and display each

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Talking find differences between 2 files and display each

    Hi all,

    I have been stuck at this for 2 days now. Hope someone can show me the way.

    I have 2 worksheet namely 'D' and 'D1' (e.g. D being 18 may and D1 being 17 may)
    I have some conditions that will be met, and the overall sumproduct of D will minus overall sumproduct of D1.

    I would like to display those differences. (e.g. the '-1' highlighted in green in the formula worksheet)

    please refer to attached sample.
    sample1.xls
    Last edited by melvyndb; 04-24-2012 at 09:01 PM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    I guess you forgot to highlight the rows in green. Can you point out the cells / ranges?

    Also, do you just want the differences to be highlighted in color?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi Arlette,

    Thanks for replying.

    I was editing the results, so turned out that the '3' should actually be '-1'.

    As for the highlighted in green, i meant I want to display the '-1' results particular rows. (that is highlighted in green)

    so for example, the box that is highlighted in green shows '4' instead (under formula worksheet), there show be 4 rows that should be displayed (the 4 rows can be displayed under the formula worksheet). It does not matter if the rows are highlighted, i just need to know which rows are the differences, whichever way is good for me.

    Hope that clarifies, thank you.

    regards,
    Melvyn
    Last edited by melvyndb; 04-24-2012 at 09:03 PM.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    So you only need the rows from sheet "D" to be displayed which do not match with "D1" ?

  5. #5
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi arlette,

    Actually, strictly speaking, i need ANY differences from the two worksheets to appear. sometimes D1 might have some rows not found in D and vice versus. (e.g. D minus D1 = -5)

    In that example, I also need to view the -5 rows from D1. Hope that clarifies.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    Ok, I am almost done creating a code for you, but am running into some issues.

    1. There are blanks in your data. Will there be blanks in your original data as well? Should these rows containing blank cells be compared as well?

    2. Is there any unique identifier to compare the rows between both the sheets?

  7. #7
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi arlette,

    There might be blanks and later on filled with status.

    As for the unique identifier, there are unique ID for each rows, but i do not use them. Because although ID 1001 might have some columns data changed, but i only want to display those rows with the conditions i am looking for.

    Hope that explains.

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    Without the unique identifier its tough to compare the rows.

  9. #9
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi Arlette,

    I almost lost all hope when you didn't reply. But I am glad you did.

    I guess i understand what you mean. Can we then incorporate the Unique ID in?

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    I am sorry, i got caught up with stuff.

    Yes, can you include the unique ID and re-attach the file?

  11. #11
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi arlette,

    I should thank you instead for coming back to this case.

    Anyway, I have attached it. Do note that in D worksheet, for my case, the rows might not be in sequence order, as data are always changing daily. So cannot compare row for row. should be dynamic to search for the UID and its conditions, thanks.

    sample1(1).zip

    regards,
    Melvyn

  12. #12
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi Guys,

    any idea on this case?

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    Melvyn,

    Sorry to bother you. Can you please attach the .xls or .xlsx file without the zip? I have a problem with zip files on my system.

  14. #14
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    Hi Arlette,

    Sorry, but im in my office which does not allow me to post up without zipping. sorry for the inconvenience.

  15. #15
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    I managed to get the file somehow. Will work out something for you.

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    Melvyn,

    Sorry for the delay. Try this code.
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button.

  17. #17
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: find differences between 2 files and display each

    No Problem at all arlette.

    I've tested and it worked as suggested. Thank you so much for taking your time off to work on my case, up reputation for a guru.

    regards,
    Melvyn

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: find differences between 2 files and display each

    Great...am happy it worked for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1