VBA code to compare data of two tables on different worksheets

  • Hey guys,


    it has been a while since I have been here and normally I manage to create working codes for documents with help of the information on the forum but at the moment I am stuck and can't seem to find a good code to do what I want.


    I have two worksheets with data which are a dumps form our ERP software, I have imported the excel data into two sheets and placed them in tables (one sheet, one table).
    The data contains items which still have to be purchased and parts that have already been ordered, some of these parts have an order acknowledgement (OA) and some are still pending for an OA.


    To keep a good overview of which items still need to be purchased or still have to be followed up for an OA, I would like to load a new dump from our ERP and compare it with the last dump (let's say a week before).
    The new data will be imported on sheet 1 and the historic data can be found on sheet 2.


    I would like to check which rows of the new dump are present in the table on sheet 2 and if so, see what items have been changed.
    Important is to check if values of 3 different columns in table 1 are present in table 2, only then this row item is the same item as in table 2. Please note that the rows can be anywere, so row number 3 on sheet 1 can correspond with row 10 on sheet 2.


    [ATTACH=CONFIG]73313[/ATTACH]


    [ATTACH=CONFIG]73314[/ATTACH]


    The first image shows the table on sheet 2 with historic dates, the second image shows the table with the new dump on sheet 1.
    In yellow the columns are highlighted which will need te be used to check if data from 1 row in table 1 is present in table 2 on sheet 2.
    In the second image you see that particular cells are highlighted, these cells contain data which is different (blue) or a complete row that is not present in the historic data (red).


    I have tried a lot of codes but cannot figure out the correct approach. See below the code I have ended up with, but maybe a different approach would be better.



    Hopefully you can help me out with the code, I have been trying for a few days. Your help is appreciated!


    Best, Ruud

  • Re: VBA code to compare data of two tables on different worksheets


    Hello Stephen,


    The code is partially working and not finished. But I need help on the first section of my idea, and that is the code from above.
    Pasting the workbook as is right now would not be possible because of the data which is present in it, then I would have to re-write it (confidential).


    Let me try and rephrase:


    1. In sheet 1 I load data into table 1 which I want to compare by pressing a commandbutton with the data in table 2 on sheet 2
    2. I want to check if each row of table 1 is present in table 2
    3. To determine if the exact same row of table 1 is available in table 2 the data in the row of at least 3 columns should be found in table 2 (in this case column D, E and G)
    4. The start row is row 3, but this row does not directly correspond with row 3 in table 2, this could be any row in table 2
    5. When the data of 3 columns cannot be found it means the particular row in table 1 is not present in table 2 » this should be followed by highlighting the complete row in table 1
    6. When the data is found, I want to compare the rows in table 1 and 2 and highlight all items which are different


    At the moment I am stuck at bullet 3 and 4.
    - With the code below I am comparing both tables: find where rCell (row in table 1) is present in table 2. Return the row number (iRow) where this value is found in table 2



    - Then with rCell offset check whether the other columns of the particular row are also present in table 2 » By using the column number
    and adding iRow and using the offset:

    Code
    1. rCell.Offset(0, -1).Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Offset(0, -1).Value


    The code will highlight some rows (just for me to see if the code is working) but the code seems to random highlight data in column D and E and not actually check whether the data of the row in table 1 is actually present in table 2


    Hopefully you understand my question a little better.


    Thanks in advance.

  • Re: VBA code to compare data of two tables on different worksheets


    Stephen,


    Find attached the workbook, upon changing the data a little and working on the code some more it looks like the code is doing the inverted of what I want (partially). Although it should highlight the cells in green as well as red it only colors the cells red.
    In the attached workbook the blank cells should have been the red colored cells and in the red colored rows only columns D, E and G should have been colored green.


    ozgrid.com/forum/core/index.php?attachment/73333/


    The code is behind 'Check data'.


    Hopefully you can help me out.


    Thanks

  • Re: VBA code to compare data of two tables on different worksheets


    OK, that helps. Just to be sure, if a row in the 1st sheet it doesn't exist in the 2nd sheet (based on D,E and G) then the whole row should be coloured red in sheet 1. If a row exists in both, should the row still be coloured red in sheet 1 and the similarities coloured green?


    Edit - and another question - you're actually checking the purchase order number first and then cols D, E and G?

  • Re: VBA code to compare data of two tables on different worksheets


    - If the row doesn't exist it should be colored red
    - If the row does exist based on columns D, E and G then I want to check each individual column of that row and only color the cells that are different so I know what is different in the new data compared to the historic data
    (in the code I used the color green only for the D, E and G column for my reference to see if the code would do what I want) but the actually idea is only color the cells that are different
    - With the current code I am actually checking column E first, then D and then G, but it could also have been D, E, G. (I ended up with this order because I was messing around with the code) but all these values should be present in table 2

  • Re: VBA code to compare data of two tables on different worksheets


    Can you try this and let me know how you get on. I'd be surprised if it's the finished product, but at least will know if on the right lines

  • Re: VBA code to compare data of two tables on different worksheets


    - The rows with article 1, 2, 3, 4 and 5 are ok (these correspond with Row 3, 4, 5, 6 and 7)
    - Article 6 and 7, these complete rows are colored red, but are present in table 2 » The columns K and L are colored green and this is ok because the data is different compared to table 2
    - Article 8 is OK
    - Article 9 is NOK, is colored red but is present in table 2 » so should be transparent
    - Article 10 is OK
    - Article 11 is OK


    [ATTACH=CONFIG]73338[/ATTACH]

  • Re: VBA code to compare data of two tables on different worksheets


    Different approach

  • Re: VBA code to compare data of two tables on different worksheets


    Stephen,


    Thanks a million, almost there!! It does almost everything I have asked for. I have checked the code and changed the cell and found columns from A to D, because the check is based on the presence of column D, E and G.
    I have added some comments for myself in the code below for my own understanding of what you have written, I also mentioned some questions for which I would like to receive some explanation what you have done there.
    Finally at the end of the code I get an error (Run-time error '13': Type mismatch), and I think it has to do with the offset of the range on sheet 2, see asterix.


    Hopefully you can help me out with the last bit as well, thanks a lot.


  • Re: VBA code to compare data of two tables on different worksheets


    Yes it was slightly more complicated than I first thought because if you get a match on col A and it matches DEG it doesn't shade but then if you get the same value in A on sheet 1 and it doesn't match DEG it shades it red, 'over-writing' the unshaded row. Hence, the code first does the shading for when col A is not found at all or it is found but DEG do not match, but it stores the address of where A matches DEG and does that at the end. I'll come back and have a look at what you've done.

  • Re: VBA code to compare data of two tables on different worksheets


    While yesterday I have send the reply from home, now at the office I copied the changed code, that I have posted yesterday, in the file at work and it seems to run without the error message.


    When I am back at home I will try again and see if the issue persist yes or no. Very strange, the only issue here is that my cells lock up, so I am not able to enter any text after running the macro, when I switch to another worksheet and back I am able to type into the cells again. I have had this before, any ideas?

  • Re: VBA code to compare data of two tables on different worksheets


    So I checked it at home and it works without an error, so that is awesome, thanks Stephen. No idea what happened yesterday.


    But ...


    The bad news, I added the code in my original workbook and did a run on the data, it did seem to work until I have added more and more data in it. In one way or another, when I have data in table 1 and copy the same data to table 2 and compare the data you would think all cells will be blank, wrong .. it still highlight a few cells in columns H, I, K and L. Can't figure out why.


    Could you explain the last 'For' loop so I have a better understanding of what is going on there?


    Maybe with some additional information on the code I can figure out what to do .. I have the feeling maybe it has to do with some duplicates, I have added multiple columns to the condition (D, E and G » now also B and C) but I cannot define any more. And I am pretty sure the exact same data of that row in table 1 is present in table 2.


    Unfortunately I am not able to create the same issue with the dummy workbook.


    I will try some more in the meantime, hopefully you have an idea? .. I was thinking to add the row number to an additional column, let's say Q on sheet 1, so I atleast know were the row was found in table 2 and maybe start from there to find the issue.

  • Re: VBA code to compare data of two tables on different worksheets


    Okay, so after putting the found.address in column Q by 'cell.Offset(, 13) = found.Address' and running the code, I figured out that it was duplicate data that was present. In my workbook there are parts that are used for the same project but used in a different sub assembly. So one way or another I will have to add an additional check condition.


    Stephan, thanks again for helping me out and if you can elaborate a little more on the last 'For' loop, that will be appreciated.

  • Re: VBA code to compare data of two tables on different worksheets


    If b is True then it means at least one row in sheet2 matched the sheet1 entry across all 4 columns, and the respective column A addresses are stored in the array v, and this loop will be executed. The loop iterates through each element of v, removes any shading from the row, and then compares each cell in that row across both sheets and if any pair is different it shades the sheet1 cell green. If you step through the code using F8 you will see the actual values as it runs.

  • Re: VBA code to compare data of two tables on different worksheets


    Stephen, thanks a lot for the explanation as well as the complete code. I managed to rebuild my complete workbook and changed the code accordingly, so using different columns and colors.
    So the code in post #12 is a correct working code. Based on the info I mentioned in post #18, I have added 1 additional condition column.


    This thread can be set to solved. Thanks again, really appreciated your help!