VBA code to compare data of two tables on different worksheets

Maurush · Aug 2nd 2017

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.

Code

Private Sub CMD_Check_Data_Click()


Application.ScreenUpdating = False


Dim Compare_Range_Dump As Range


Dim iRow As Long
Dim LastRow As Long
Dim rCell As Range
LastRow = Range("A:N").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row




    With Worksheets("DUMP Inkopen per productieorder")
            
         Set Compare_Range_Dump = .Range("E3:E" & LastRow)
         
    End With
                        
        If IsEmpty(Range("A3")) Then
               
            MsgBox "No data to check", vbOKOnly + vbInformation, ""
        
        Else
             
            For Each rCell In Compare_Range_Dump
            
                Dim i As Integer
                
                For i = 1 To LastRow
                    
                    If rCell.Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & i).Value Then


                        iRow = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & i).Row 'Row where value was found
                        
                            If rCell.Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Value & _
                                rCell.Offset(0, -1).Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Offset(0, -1).Value Then
                            
                                rCell.Interior.Color = RGB(102, 204, 0)
                                rCell.Offset(0, -1).Interior.Color = RGB(102, 204, 0)
                            
                            Else
                        
                                rCell.Interior.Color = RGB(255, 0, 0)
                                rCell.Offset(0, -1).Interior.Color = RGB(255, 0, 0)
                            
                            End If
                    End If
                Next i
            Next
            
                MsgBox "Check done", vbOKOnly + vbInformation, ""
        
            End If


Application.ScreenUpdating = True


End Sub

Display More

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

Best, Ruud

Maurush · Aug 3rd 2017

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

Anyone? Help is appreciated.

Best, Ruud

**StephenR** · Aug 3rd 2017

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

Not sure what you're asking - does your code work? In any case, please post a workbook rather than pictures.

Maurush · Aug 3rd 2017

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

Code

For Each rCell In Compare_Range_Dump 
             
            Dim i As Integer 
             
            For i = 1 To LastRow 
                 
                If rCell.Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & i).Value Then 
                     
                    iRow = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & i).Row 'Row where value was found
                     
                    If rCell.Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Value & _ 
                    rCell.Offset(0, -1).Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Offset(0, -1).Value Then 
                         
                        rCell.Interior.Color = RGB(102, 204, 0) 
                        rCell.Offset(0, -1).Interior.Color = RGB(102, 204, 0) 
                         
                    Else 
                         
                        rCell.Interior.Color = RGB(255, 0, 0) 
                        rCell.Offset(0, -1).Interior.Color = RGB(255, 0, 0) 
                         
                    End If 
                End If 
            Next i 
        Next

Display More

- 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

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.

**StephenR** · Aug 3rd 2017

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

Post a workbook with a small sample of dummy data - as long as it represents your actual data that's all that's needed. It will speed things up enormously.

Maurush · Aug 3rd 2017

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.

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

The code is behind 'Check data'.

Hopefully you can help me out.

Thanks

**StephenR** · Aug 3rd 2017

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?

Maurush · Aug 3rd 2017

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

**StephenR** · Aug 3rd 2017

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

Code

Sub multmatch()
     
Dim cell As Range
Dim found As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim s As String
Dim c As Long
Dim b As Boolean


Set ws1 = Sheets("DUMP Inkopen per productieorder")
Set ws2 = Sheets("Inkopen per productieorder")


ws1.Range("A1").CurrentRegion.Offset(2).Interior.Color = xlNone


For Each cell In ws1.Range("A3", ws1.Range("A" & Rows.Count).End(xlUp))
    Set found = ws2.Range("A3", ws2.Range("A" & Rows.Count).End(xlUp)).Find(cell, LookAt:=xlWhole)
    If Not found Is Nothing Then
        s = found.Address
        Do
            If cell.Offset(, 3) = found.Offset(0, 3) And _
               cell.Offset(, 4) = found.Offset(0, 4) And _
               cell.Offset(, 6) = found.Offset(0, 6) Then
                b = True
                For c = 0 To 13
                    If cell.Offset(, c) <> found.Offset(, c) Then
                        cell.Offset(, c).Interior.Color = vbGreen
                    End If
                Next c
                Exit Do
            Else
                cell.Resize(, 14).Interior.Color = vbRed
            End If
            Set found = ws2.Range("A3", ws2.Range("A" & Rows.Count).End(xlUp)).FindNext(found)
        Loop While found.Address <> s
    End If
    If Not b Then
        cell.Resize(, 14).Interior.Color = vbRed
        b = False
    End If
Next cell
     
End Sub

Display More

Maurush · Aug 3rd 2017

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]

**StephenR** · Aug 3rd 2017

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

Different approach

Code

Sub multmatch()
     
Dim cell As Range
Dim found As Range
Dim r1 As Range, r2 As Range, ws1 As Worksheet, ws2 As Worksheet
Dim s As String
Dim c As Long
Dim b As Boolean, v(), i As Long, wf As WorksheetFunction


Set wf = WorksheetFunction


Set ws1 = Sheets("DUMP Inkopen per productieorder")
With ws1
    Set r1 = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
End With


Set ws2 = Sheets("Inkopen per productieorder")
With ws2
    Set r2 = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
End With


ws1.Range("A1").CurrentRegion.Offset(2).Interior.Color = xlNone


For Each cell In r1
    Set found = r2.Find(cell, LookAt:=xlWhole)
    If found Is Nothing Then
        cell.Resize(, 14).Interior.Color = vbRed
    Else
        ReDim v(1 To r1.Count, 1 To 2)
        s = found.Address
        Do
            If cell.Offset(, 3) = found.Offset(0, 3) And _
               cell.Offset(, 4) = found.Offset(0, 4) And _
               cell.Offset(, 6) = found.Offset(0, 6) Then
                b = True
                i = i + 1
                v(i, 1) = cell.Address
                v(i, 2) = found.Address
            Else
                cell.Resize(, 14).Interior.Color = vbRed
            End If
            Set found = r2.FindNext(found)
        Loop While found.Address <> s
        If b Then
            For i = LBound(v, 1) To UBound(v, 1)
                If Not IsEmpty(v(i, 1)) Then
                    ws1.Range(v(i, 1)).Resize(, 14).Interior.Color = xlNone
                    For c = 0 To 13
                        If ws1.Range(v(i, 1)).Offset(, c) <> ws2.Range(v(i, 2)).Offset(, c) Then
                            ws1.Range(v(i, 1)).Offset(, c).Interior.Color = vbGreen
                        End If
                    Next c
                End If
            Next i
        End If
    End If
    b = False
    i = 0
Next cell
     
End Sub

Display More

Maurush · Aug 3rd 2017

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.

Code

Dim cell As Range
    Dim found As Range
    Dim r1 As Range, r2 As Range, ws1 As Worksheet, ws2 As Worksheet
    Dim s As String
    Dim c As Long
    Dim b As Boolean, v(), i As Long, wf As WorksheetFunction
     
    Set wf = WorksheetFunction
     
    Set ws1 = Sheets("DUMP Inkopen per productieorder")
    With ws1
        Set r1 = .Range("D3", .Range("D" & Rows.Count).End(xlUp))   'Changed from A to D
    End With
     
    Set ws2 = Sheets("Inkopen per productieorder")
    With ws2
        Set r2 = .Range("D3", .Range("D" & Rows.Count).End(xlUp))   'Changed from A to D
    End With
     
    ws1.Range("D1").CurrentRegion.Offset(2).Interior.Color = xlNone     '-->> Could you tell me what this does? Do I need to change this from A to D as well?
     
    For Each cell In r1
        Set found = r2.Find(cell, LookAt:=xlWhole)
        If found Is Nothing Then
            cell.Offset(, -3).Resize(, 14).Interior.Color = vbRed   'I have added Offset(,-3) to be able to color the complete row starting from column A
        Else
            ReDim v(1 To r1.Count, 1 To 2)      '-->> What does the 1 To 2 do in this code??
            s = found.Address       'Found item in table 2
            Do      'Check column D, E and G to see if items in this row correspond to items in found row in table 2
                If cell.Offset(, 0) = found.Offset(0, 0) And _
                cell.Offset(, 1) = found.Offset(0, 1) And _
                cell.Offset(, 3) = found.Offset(0, 3) Then              'Changed the offset from 3, 4 and 6 to 0, 1 and 3
                    b = True
                    i = i + 1       '-->> What does this do??
                    v(i, 1) = cell.Address      'Look up item in table 1
                    v(i, 2) = found.Address     'Found item in table 2
                Else
                    cell.Offset(, -3).Resize(, 14).Interior.Color = vbRed   'I have added Offset(,-3) to be able to color the complete row starting from column A
                End If
                Set found = r2.FindNext(found)
            Loop While found.Address <> s
            If b Then
                For i = LBound(v, 1) To UBound(v, 1)        '-->> Could you explain the last 'For' section, I have an error, see asterix below
                    If Not IsEmpty(v(i, 1)) Then
                        ws1.Range(v(i, 1)).Offset(, -3).Resize(, 14).Interior.Color = xlNone        'I have added Offset(,-3)
                        For c = 0 To 13
                            If ws1.Range(v(i, 1)).Offset(, c) <> ws2.Range(v(i, 2)).Offset(, c) Then        '* ERROR: I think it has to do with the ws2.Range and the offset which I have set to (,-3)
                                ws1.Range(v(i, 1)).Offset(, c).Interior.Color = vbGreen
                            End If
                        Next c
                    End If
                Next i
            End If
        End If
        b = False
        i = 0
    Next cell

Display More

**StephenR** · Aug 4th 2017

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.

Maurush · Aug 4th 2017

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

Ok I understand, still was not able to figure out the correct way to proceed, so if you can have a look that would be great.

**StephenR** · Aug 4th 2017

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

What are the values of c, v(i,1) and v(i,2) when it errors? Would have been easier if you'd posted a workbook as is, as then wouldn't have to make these changes.

Maurush · Aug 4th 2017

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?

Maurush · Aug 4th 2017

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.

Maurush · Aug 4th 2017

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.

**StephenR** · Aug 7th 2017

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.

Code

If b Then 
            For i = LBound(v, 1) To UBound(v, 1) '-->> Could you explain the last 'For' section, I have an error, see asterix below
                If Not IsEmpty(v(i, 1)) Then 
                    ws1.Range(v(i, 1)).Offset(, -3).Resize(, 14).Interior.Color = xlNone 'I have added Offset(,-3)
                    For c = 0 To 13 
                        If ws1.Range(v(i, 1)).Offset(, c) <> ws2.Range(v(i, 2)).Offset(, c) Then '* ERROR: I think it has to do with the ws2.Range and the offset which I have set to (,-3)
                            ws1.Range(v(i, 1)).Offset(, c).Interior.Color = vbGreen 
                        End If 
                    Next c 
                End If 
            Next i 
        End If

Display More

Maurush · Aug 8th 2017

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!

VBA code to compare data of two tables on different worksheets

Participate now!

Share

Users Viewing This Thread