Compare 2 Worksheets - Show List of Variances on 3rd Sheet

  • This code works great but it's not enough - I'm hoping this code can be modified to accommodate a larger range of content (A:W) doing the following:
    Sheet4 is considered the "Original"
    Sheet5 is the sheet that someone has made changes to
    Sheet 6 is where the list of all found changes are posted (along with the cell refc of where the changed cell was found in Sheet5)
    Compare Sheet 4's Column A (A2) to Sheet 5's Column A (A2)
    Compare Sheet 4's Column B (B2) to Sheet 5's Column B (B2), etc. out through Column W (the format of both sheets will be identical)


    It's one directional (no changes will ever be made to the "Original" Sheet4)
    It's possible new rows will be added at the base of the evolving Sheet5 but no inserting of rows - so existing content should align nicely for comparisons.


    Hopefully, the attached small sample file will help everyone see what the current code does:
    (it is tied to Sheet1 being the Orig, Sheet2 being the changing sheet, Sheet3 is what the code found).


    The new example of what I'm hoping someone knows how to achieve is found on Sheets 4, 5, & 6 (done manually)
    My real file has approx. 20 columns of a lot of categorical data so listing everything going downward on Sheet 6 was much easier to read..
    Having a cell refc listed makes it much easier to scan the findings and determine whether or not it's okay to retain the change made (and where to locate it quickly if not)


    Thanks greatly!


  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    I am a bit confused, does you actual file have all 6 sheets or just 3 (which would be sheets 4, 5, and 6 in the sample workbook)?


    If all 6 sheets then you need to compare sheets 1 and 2 with results going to sheet 3, then compare sheets 4 and 5 with results going to sheet 6?


    Or, if actual file is just 3 sheets then treat the sample file sheets as though they were sheets 1, 2 and 3 in actual file?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    Sorry for the confusion. The actual file would only have 3 sheets.
    (They can be called whatever you want as I'll probably need to edit the names to match the real file - but if you're using my attachment, I'd say utilize Sheets 4,5,6.)


    In the sample, I was trying to demonstrate what "this" code does (using Sheets 1,2,3)
    and then created 3 more sheets (Sheets 4,5,6) to demonstrate what I really need the code to be able to do ..
    ...(with more columns present like shown in Sheets 4 & 5)
    ...(with a list format results going downward like in Sheet 6)
    ...(to include a cell refc like shown in Sheet 6)


    Thank you in advance for your help!

  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    OK I understand now.


    The first attached file ("Compare-Changes_on_Sheet") will do as your sample file (list the changes on the "Changes" sheet). Click the button on The "Current" sheet to run the code. Note that your sample file with the manually listed changes should have included Cells B6 and B7 which are additional rows and the new values are "reptile" and "insect".


    Code assigned to the button is


    Quote

    Having a cell refc listed makes it much easier to scan the findings and determine whether or not it's okay to retain the change made (and where to locate it quickly if not)


    Also attaching a file ("Compare-Changes_as_Comments") with a different approach you may want to consider, the changed cells are indicated (I used a red border, but you can change that) and a cell comment is added which will show the original value. This will save you having to switch between sheets to get the next changed cell reference and it's original value. Cells that are in rows that are not on the Original sheet (ie. the additional rows you mentioned) will have "New Row" as the cell comment.


    The code assigned to the button for that is


    Note that in both files I changed the sheet names and code names to "Original", "Current" and, for the first file "Changes". The code names are then used in the code so it does not matter if you change the sheet names or their position, the code will still work on the correct sheet(s). Leave the code names unchanged! If you move the code to another workbook then you will have to change the code names for the sheets involved in that workbook (use the same code names that I used though).

  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    UN-be-LIEVE-able!
    Love both of them! - Now I'm so torn - as to which method to use...

    I can see that I will surely utilize both - depending on the project and who the reviewer of changes might be ---


    This brings to mind a question:
    With the 2nd sample you posted (where changes are nicely stored in an easily hoverable comment) -- is it remotely possible for the code to also record WHO the person was that made the change within that same comment?
    (record whatever name their computer name/user ID is)? (similar to how track changes does) - but many of the folks involved aren't familiar w/ using that - so we don't want to utilize that tool -- (forced to accept/deny/turn on/off, etc.)


    I know in some cases it's an actual person's name that shows up for the computer user: "John Doe", on other cases - it's like a user ID code: "JD005" - but either way -- it would help drastically if it's possible?


    I know the computer name is stored in the System Properties -but the C:\Users\xyzuser's name would probably be more accurate - with the tie directly to a user... over my head -- don't sweat it if it's over yours too! =-)


    BACKGROUND:
    We're having to send a large file to a corporation that has several teams involved in an IT refresh project.. Each of these teams will be reviewing the content, it's categories, it's priorities, tier levels, etc. within that file and making changes to any cells that they feel need changed...


    Knowing which person made X change would be incredibly helpful in the event there was a conflict of interest -- where 1 team might say "this data's category should be categorized as YYY" and the original team's content had it listed as "BBB" category.


    (just trying to think ahead - following the point where changes have been made and the final management reviewer may say - WHO THE HECK CHANGED THIS TO "YYY"? We'd have to send out communication to find out which team made the change and find out why/their validation as to whether the change is validated or not...) Having a UserID or Computer Name would make back-tracking a bit easier --


    If it's not possible -- that's ok -- these two As Is -- are BEYOND AWESOME and will make multi-team collaboration of this monster file much more manageable and digestible following everyone's review/change process.


    THANK YOU -- - TERIMA KASIH !

  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    HELP I GOT AN ERROR
    Been doing some testing - and moved the code over to my real working file - but encountered an error. (see attached image)
    Is there something I need to edit to ensure the full range of a sheet is looked at?
    The range of the sample was quite small -- the range of my real sheet is: A1:W41.
    It appears the code is working as it INDEED highlighted changes and the comments reflect the previous state accurately --
    But - the error?? not sure what's wrong...?


    As you can see from the attached image (I changed the real files tab names to match "Original" and "Current"..
    I also updated the VBA properties to eliminate the reference of "Sheet1(Original)" and "Sheet6(Current)"
    to instead reflect: "Original (Original)" and "Current(Current)"


    The real sheet has headers just as the sample -- so I think it's safe to rule that out...
    The real sheet has filtering turned on -- would that affect things? Should I turn it off before running the code?
    The real sheet utilizes drop-down data validation lists to populate (using a separate sheet holding lists to pick from) but don't think that should matter if it's just looking at comparing cell XX against cell XX and evaluating whether it matches or incurred change..


    Not sure how to fix it to correct the 'range' problem.. Hope you or someone can help me resolve..
    Thank you!
    [ATTACH=CONFIG]71495[/ATTACH]

  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    Try this.


    There had to be quite a few changes to get code to work for multiple users making changes. If a cell is changed multiple times by different users only the latest user to make a change will be in the comment for that cell. There is now code in the "Current" sheet Worksheet Object Module for a Worksheet_Change Event.


    To test change cell B3 before clicking the "Show Changes" button, then click the button and you should see your user name in the comments for cell B3. The other user names are just ones that I added manually.


    The file will work by recording the cell reference and users "user name" every time a cell within the data table is changed on the new sheet that I have added. You will need to make an identical sheet in your actual workbook and give it the code name "Users". Keep Row 1 as it is with those headers. That sheet also contains the password (more about that in a bit!), there a 4 named ranges on the sheet, of them are dynamic, make sure you replicate those named ranges with exactly the same names and "Refers To". This sheet should be hidden in your actual workbook. Don't forget to copy across the code in the "Current" sheet Worksheet Object Module!


    There are also 3 new buttons, the first new one to revert any selected cells to their original value (and remove border and comment), the next to remove all borders and comments (clicking "Show" again will put them back). The final new button is for the reviewer to accept the data as the final version, all borders and comments will be removed and the list of users who made changes will be cleared .Clicking "Show" after this last new button has been clicked will do nothing, records of all changed cell references and who made the changes will be erased. Hence the reason for a password in order to run the code assigned to the "Accept" button. The current password is "ChrisOK", that can be changed on the "Users" sheet.


    It is hard to now why you were getting the error on your actual file without seeing the file. However, I have modified the code to use "CurrentRegion" rather than "UsedRange". Hopefully, that will resolve the issue, if not let me now.

  • Re: Compare 2 Worksheets - Show List of Variances on 3rd Sheet


    Oh wow! You really did something kool -- I didn't really expect it to track it the way you did it -- but it's still very kool.
    Sadly, I can't replicate it in my real file without generating an error. After starting over several times I finally decided to take the actual sample file that works -- and just pasted in my content (into Orig and Current tabs) - (and pasted as 'Values' to strip the formatting)


    I'm thinking this is going to work and SO excited - but ARGGH!! Another error! (image attached)
    The only things I did to the sample file was:
    1- File > SAVE AS, renamed it...
    2- Copied the range of content (A1:W41) to the ORIG tab
    3- Moved the buttons on the CURRENT tab out further to the right (Column Z area)
    4- Copied the same range of content (A1:W41) from the ORIG tab to the CURRENT tab
    5- Saved
    6- Went to CURRENT tab, changed cell A4 and C4 by typing the word "changed" into both..
    7- Then clicked the "SHOW CHANGED" button and BAM a different error than I was getting when I built it from scratch!
    Error 1004: Application defined or Object defined error
    (and this is the line that's highlighted): With .Parent.Range(z(1, i))


    I repasted the content into "CURRENT" for a fresh start, hit save and thought I'd try clicking the "SHOW CHANGES" button WITHOUT making any changes
    This generated the same Error 1004: Application defined or Object defined error
    (same line is highlighted as the previous run when I DID make 2 changes)


    I figure it's easier to trouble shoot this -- (knowing I was using the file you attached) as a baseline -- because you wouldn't have to figure out what I might have forgotten to do --- you know you had it all working... so the question is -- WHY once I pasted in the content - did it whack out on me..?


    I had an epiphany thinking maybe it was because the headers along the columns were diff than the sample file...
    So, I went and changed the first few headers to match the sample file.. but then this generated a new/diff error:
    Runtime Error 13: Type Mismatch (on the below line)
    sChanger = Application.VLookup(z(1, i), [User_Name_Lookup], 2, 0)


    So I'm clueless - hopefully the above will help with figuring this out...
    Seems like there's something related to the range of content (A1:W41) that is now causing an issue..

  • Hello KjBox

    this is a great solution. utilized it myself with a bigger data set. (Compare-Changes_on_Sheet.xlsm)


    However for some reason the changes output does not list the actual cell reference anymore but instead uses Symbol & Number.


    my datasets span 700 rows and 63 columns. Would you have any idea?




    Location Original Value Changed Value
    }2 0.35 $0.44
    }3 0.1 $0.20
    }4 0.65 $0.74
    }5 0.54 $0.61
    }6 0.18 $0.25
    }7 0.18 $0.23
    }8 0.7 $0.74
    }9 42 $42.02
    }10 0.09 $0.15
    }11 0.54 $0.64
    }12 0.6 $0.64
    }13 0.15 $0.23
    }14 1.5 $1.58
    }15 1.5 $1.52
    }16 0.25 $0.33
  • I suspect it is because of formatting that has been applied to column A.


    I would need to see a copy of your file to investigate further.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank, I did have the source data formatted as Table. I removed the format and ran it as plain but am getting the same Symbol/# results. sample file attached.


    Your time and help is much appreciated. Could the code find the respective header and corresponding row reference as well? e.g. results is E2 and then show the header of Column E (Row 1) and Row ID from Row 2 (Column A)?

  • Only the last three. but since the results are by cell ID, i would need the information from specific columns to be displayed as well.


    e.g. if a change in value is found in E2, i would need 3-4 columns to show information from that row 2 in the current value sheet.


    Location E2 - Original Value - Current Value - Identifier Info (Column F) - Origin Country (Column AA)


    the headers in these columns will always be same but their location might change. i used segments of another code to add to yours. I get the result but it's not flexible. e.g. i added columns for row ID, origin country and origin region

  • I am confused, you say you need to consider just the last 3 columns for changes, yet the example you give refers to a change in E2.


    Can you manually fill in a few rows of the "Changes" sheet with desired results. Plus an explanation of where those results come from.


    Thanks.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hello KjBox, sorry for not getting back to you sooner. Your code is very helpful and I am trying to use it for my situation in the best way possible.


    I amended it a bit and added code to find column headers and add them to your changes layout since I would need to more details than just the cell location. (e.g. Origin Country, Origin Region)


    I attached the file i am working with.


    Would there be a way to check for the variances but to keep the original row layout? So if the original table goes from Row 1 to 20 with 3-4 columns to be compared, the changes report would show Row 1 to 20 and also list the original / current value for each analyzed column.


    Lane ID Origin Region Origin Country Origin Pick up Original Origin Pick up Current Deviation Origin Handling Original Original Handling Current Deviation2 etc….
    1 EURO IT 0.35 0.35 0%
    2 EURO IT 0.1 0.2 100%
    3
  • The formula you are using to derive the Deviation is going the throw up a #Div0 error when a value in the Original table is zero or empty. What do you want to do about that?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You show the data in the New Layout in a completely different order to either the Original or Current data, do you want the New Layout data sorted?


    I see from your code that you are ignoring that error.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • The formula you are using to derive the Deviation is going the throw up a #Div0 error when a value in the Original table is zero or empty. What do you want to do about that?

    I added On Error Resume Next since it's a new entry against previously no entry. But on second thought, instead of Deviation % it could show the comment "New Entry"

  • Try this.


    It assumes that the Original and Current columns A, B and C will always be identical.

    This is great. Thank you very much. Could the macro be flexible in the sense if there are a varying # of columns (identical) in Sheet 1 & Sheet 2 and keep expanding as needed in Sheet 3?


    DGF Lane ID Origin_
    Region
    (enter AP, AM, EURO, MEA)
    Origin_
    Country
    (in 2-letter codes)
    Origin Pickup Origin Handling OTHC Test 5 Test 6 Test 7 Test 8 etc….
    1 EURO IT $ 0.35 $ - $ 0.08 1 3 8 10
    2 MEA AE $ 0.10 $ 0.06 5 1 1 8
    3 AP CN $ 0.65 $ 0.01 $ 1.28 8 1 5 9
    4 AP CN $ 0.54 $ 0.01 $ 1.44 7 9 4 5
    5 EURO DE $ 0.18 $ - $ 0.05 9 4 4 9