+ Reply to Thread
Results 1 to 18 of 18

Compare two worksheets and present differences in a third

  1. #1
    Registered User
    Join Date
    07-24-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    91

    Unhappy Compare two worksheets and present differences in a third

    Hi all, I have been spending many hours looking for some code to do some relatively simple comparison task but I haven't been able to find an answer to my particular problem.

    I have 2 worksheets "today" and "yesterday", each with the same number of columns but potentially different number of rows (each row is a different project). The structure of each sheet is:

    NAME // IDNUMBER // COST YEAR1 // COST YEAR2 // MANY OTHER COLUMNS
    aa // ABT-100 // 100 // 800 // etc...
    bb // SRT-876 // 245 // 445 // etc...
    and so on... (please note that the IDNUMBER has a string of letters and numbers separated by highphens).

    I am looking for some VBA code to compare "yesterday" and "today" worksheets for any changes by ID NUMBER and paste any item that has changed in a third worksheet "changes" with some highlighting:

    1) any item (IDNUMBER) that is in "yesterday" but not in "today" should be copied in the worksheet "changes" with the whole row highlighted in red (dropped item).
    2) any item (IDNUMBER) that is not in "yesterday" but that is in "today" should be copied in the worksheet "changes" with the whole row highlighted in green (new item).
    3) any item (IDNUMBER) that is both in "yesterday" and "today" should only be copied in the worksheet "changes" when some cell of the row has been modified (i.e. difference between "yesterday" and "today"), in which case, the modified cells should be highlighted in yellow. Ideally, when some changes occured for an item, I would like to be able to present both values in the same cell next to each other ("yesterday",s value in red and "today"'s value in green).
    4) do nothing with items that haven't changed between "yesterday" and "today".

    I am guessing that this would probably involve some pretty intense coding so efficiency of the code is probably important here.

    I have tried my best to do this but just can't do it!

    Please!!!! Can someone help me!!!

    Many thanks.
    Attached Files Attached Files
    Last edited by olivierpbeland; 07-24-2011 at 02:30 PM.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,

    Welcome to the Excel Forum.


    Should the ID GAEY-05-2012-v1, in both sheets Yesterday and Today, contain the same Name?


    If NOT, which name should we use?


    You have:

    Worksheet Yesterday:
    Name 11, GAEY-05-2012-v1, 7, 7, 7, 7, 7, 35

    Worksheet Today:
    Name 9, GAEY-05-2012-v1, 8, 9, 10, 11, 12, 50


    Worksheet Changes:
    Name 9, GAEY-05-2012-v1, 7/8, 7/9, 7/10, 7/11, 7/12, 35/50
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    07-24-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Compare two worksheets and present differences in a third

    Thanks.
    Yes, you're right, names should be the same for each IDNUMBER. I guess I should have get rid of this variable for my example. It's better to consider NAME as just another variable.

    Do you see a way to do what I'm looking for in vba?

    olivierpbeland

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,


    Detach/open workbook CompareYesterdayToday sheets to Changes - olivierpbeland - EF785472 - SDG15.xlsm and run macro CompareYesterdayToday.


    If you want to use the macro on another workbook:


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please Login or Register  to view this content.

    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


    Then run the CompareYesterdayToday macro.

  5. #5
    Registered User
    Join Date
    07-24-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Compare two worksheets and present differences in a third

    This looks absolutely amazing.
    Gotta go but will look into it soon. A million thanks. The code looks super fast.

    olivierpbeland

  6. #6
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Compare two worksheets and present differences in a third

    Thanks for the update. If it works then please show the thread as "Solved" and if your satisfied with my help please check the "Scale"
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  7. #7
    Registered User
    Join Date
    07-24-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Compare two worksheets and present differences in a third

    Again thank you so much. This is really appreciated.
    Would I dare asking you 2 more things:
    1) Could you give directions to make it open endend column-wise? From what I've seen it's already open ended in the rows but column would be a must as rows will also likely be added and dropped often.
    2) Add a bit of comments to explain what the code does. I would say that my vba skills are somewhere in the region of 3/10.

    Again, I am simply amazed at how your code seem to be optimized and neet. Frist glance I would say it's above anything else I've seen on the subject so far. Again a huge thanks.

    olivierpbeland

  8. #8
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,

    Its late and am going off line. If you do not have a diff. answer, I answer on tuesday. I'll be gone on monday.


    Disreguard my post on this thread.
    Last edited by Charles; 07-24-2011 at 08:07 PM. Reason: Opp's disreguard my post

  9. #9
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,

    Could you give directions to make it open endend column-wise?

    Can we have another workbook with openended columns for testing?

  10. #10
    Registered User
    Join Date
    07-24-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Compare two worksheets and present differences in a third

    Sorry, I meant by that that the number of columns will be constantly varying. I would have liked the code to be able to accomodate any number of rows without having to manually modify it whenever rows get deleted/addedd.
    I'm assuming something using "End(xlup)" would do...

    Thanks.

    opbeland

  11. #11
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,

    The code works from B2 to the last used cell in column B.

    I assume that there are no blank ID's in column B in worksheets Yesterday and Today.


    Please Login or Register  to view this content.
    Last edited by stanleydgromjr; 07-24-2011 at 10:16 PM.

  12. #12
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,

    Per your Private Message:

    Today, 08:27 AM
    olivierpbeland
    Registered User Join Date: 24 Jul 2011
    Location: Ottawa
    MS Office Version:Excel 2003
    Posts: 5
    Re: Compare worksheets copy differences in a third
    --------------------------------------------------------------------------------
    Thanks Stan but I'm not sure I understand. For the moment, the column structure will be as in the workbook you worked on. However, it is likely that we will have to add (and maybe even remove) columns during the project and I had hoped that the code could be made flexible enough to
    1) determine the number of columns
    2) run the code you sent me with the appropriate number of columns.
    As such, I am not too sure to know what to send you...Could you be more specific please.
    Many thanks for your kind help!
    Olivier

    For any single project, will worksheets Yesterday and Today always have the same number of columns for that project?

    Please attach another workbook with a different number of of columns for testing?

  13. #13
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Compare two worksheets and present differences in a third

    olivierpbeland,


    I have tested the new macro by deleting the same columns in both worksheets, and by adding the same columns in both worksheets.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Please Login or Register  to view this content.

    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


    Then run the CompareYesterdayTodayV2 macro.

  14. #14
    Registered User
    Join Date
    03-21-2013
    Location
    Zagreb
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Compare two worksheets and present differences in a third

    hi...

    i have similar problem..

    I have 2 worksheets "Sheet1" and "Sheet2", each with the same number of columns but different number of rows.
    I am looking for some VBA code to compare this worksheets for any changes and paste any item that has changed in a third worksheet "changes" with some highlighting:

    1) row that is in "Sheet1" but not in "Sheet2" should be copied in the worksheet "changes" with the whole row highlighted in red
    2) row that is not in "Sheet2" but that is in "Shhet1" should be copied in the worksheet "changes" with the whole row highlighted in green
    3) when some cell of the row has been modified, row should be copied in the worksheet "changes", the modified cells should be highlighted in yellow,and both values of modificed cell shoud be in the same cell next to each other
    4) do nothing with items that haven't changed between "yesterday" and "today".

    Please!!!! help me!!!

    thanks.

  15. #15
    Registered User
    Join Date
    07-24-2011
    Location
    Ottawa
    MS-Off Ver
    Excel 2007
    Posts
    91

    Re: Compare two worksheets and present differences in a third

    Sorry I can't help you more than with directing you to the code above which works. It's been too long since I worked on this but I'm sure you can modify the code to suit your needs.

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

    Re: Compare two worksheets and present differences in a third

    nikol_28,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    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]

  17. #17
    Registered User
    Join Date
    10-25-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Compare two worksheets and present differences in a third

    I just want to thank a million. This is excatly I was looking for. Need one help. I have modified the code to check till 28 columns. It is working perfectly. But it is not coloring beyond col H. I am not able to find where to change with my limited knowledge. Could you please help?
    Thanks
    Arun

  18. #18
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,353

    Re: Compare two worksheets and present differences in a third

    @ark1234
    Did you not read arlu1201's post right above yours?
    Click on "Forum Rules" at the top and pay special attention to #2.

    Thanks and Regards

+ 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