title: Compare two tables in VBA and paste data into a third one
body: <p>I'm working since two weeks on a mini project and now, I'm stuck. I've tried many things before coming here and I hope to get some help.</p>
  
  <p>I've two tables and 3 sheets ("PT" = worhskeets1 with the first table, "PI" = Worksheet2 with the second table, and "Comparison", the third worksheets used to compare the data from table 1 and table 2.</p>
  
  <p>First and this works, when I copy the data in Cells(1, "A") in the first worksheets it automatically sort the values. Same on Worksheets 2.</p>
  
  <p>In order to help you to understand, each table has "Codes" to which in a column besides an amount is associated (amount in EUR, USD, etc).
  The problem is that codes can repeat in each column for both tables.</p>
  
  <p>In the ideal scenario, when the two tables exactly matches (same length), I made a vlookup and it didn't worked. For the repeating codes, it took only the first "matchable" corresponding amount.
  Then I tried to use the combo INDEX &amp; MATCH. It worked.
  I tried also to vlookup by making the "look up array" variable: as the codes are the same, I made a vlookup on each lines, one by one. Not very effective but as I won't have more than 50 rows it worked. The code looks like:</p>
  
  <pre><code>Dim wsi As Worksheet
  Dim wst As Worksheet
  Dim counter As Integer
  Dim NonVideLondon As Integer
  Set wsi = Worksheets("PI")
  Set wst = Worksheets("PT")
  
  NonVideLondon = wst.Range("A" &amp; Rows.Count).End(xlUp).Row
  counter = 1
  For i = 1 To NonVideLondon
  LookupCode = Cells(i, "C") 'The codes from Table2
  
  Cellnum = Application.VLookup(LookupCode, wsi.Range("C" &amp; counter &amp; ":C" &amp;NonVideLondon), 1, False)
  
  If IsError(Cellnum) Then
      Cells(i, "B") = "" 'Compared to the codes of table2
  Else
      Cells(i, "B") = Cellnum
      counter = counter +1 
  End If
  
  Next i
  </code></pre>
  
  <p>(I apologize for the approximation of the code)</p>
  
  <p>The issue is when Table 1 length > Table 2 Length and opposite.
  Here I really don't know how to do.</p>
  
  <p>On the sheet "Comparison" there are then 4 columns:</p>
  
  <p><strong>CodePI</strong>(Col B.) <strong>CodePT</strong>(Col C.)  <strong>AmountPI</strong>(Col. D)  <strong>AmountPT</strong>(Col. E)</p>
  
  <p>In Col A there are buttons to clear all the sheets and to "Verify". It's a module done to check the length of table and launch a macro if table 1 > table 2 and another if Table 2 > Table 1.</p>
  
  <p>When I do a vlookup, line by line (again, not very effective) when the two tables matched, this works and the comparison was correctly done.</p>
  
  <p>I also tried the .FindMethod: </p>
  
  <pre><code>StrFind = Cells(j, "B")
  Set fRng = Worksheets("PI").Range("C2:C50").Cells(Worksheets("PI").Range("C2:C50").Cells.Count)
  
   For i = Application.CountIf(Worksheets("PI").Range("C2:C50"), StrFind) To 1 Step -1
       Set fRng = Worksheets("PI").Range("C2:C50").Cells.Find(What:=StrFind, _
                                 LookIn:=xlValues, _
                                 LookAt:=xlWhole, _
                                 After:=fRng, _
                                 MatchCase:=True)
  
         If Not fRng Is Nothing Then
            With Worksheets("Comparison")
                .Cells(fRng.Row, "S") = fRng.Value
            End With
        End If
      Next i
  
  
  
  Next j
  </code></pre>
  
  <p>But I'm always failing at having a "correct comparison table" for all situations (Table1 = Table2, new codes, Table 1 > Table 2 and Table 2 > Table 1). Oh the reason why I'm using a comparison table, is that the amounts can something perfectly match, sometimes change for a few amounts ( &lt; 1). Then we <strong>cannot</strong> simply copy the amounts.</p>
  
  <p>I've made another codes to "see the differences" (and it works when two tables are identical in length):</p>
  
  <pre><code>If Cells(i, "C") = Cells(i, "B").Value Then Cells(i, "B").Interior.Color = RGB(146, 208, 80) _
  Else: Cells(i, "B").Interior.Color = RGB(226, 107, 10) 'FOR CODES
  
  If Cells(i, "E").Value = Cells(i, "D").Value Then Cells(i, "D").Interior.Color = RGB(146, 208, 80) _
  Else: If Cells(i, "E").Value &lt; Cells(i, "D").Value + 1 And Cells(i, "E").Value &gt; Cells(i, "D").Value - 1 Then _
      Cells(i, "D").Interior.Color = RGB(255, 226, 133) _
      Else: Cells(i, "D").Interior.Color = RGB(226, 107, 10) ' FOR AMOUNTS
  </code></pre>
  
  <p>Now I think:</p>
  
  <p>If table1 > table 2 then takes the data for codes and amount of table 1 and comparing them to table2. If there is a match (based on the position of the tables) then in the comparison sheet we will have in first column all the data from the longest table. Same for the amount (quite easy here, we only need to copy from the longest table to the comparison). </p>
  
  <p>If there are codes in the second table that match the first one, how to make the correspondence? I mean if Table 1 > Table 2 all the date from Table1 would be "simply copied" but for the matching in Table2, how to make on the corresponding line of Table 1 (in WS comparison), the correct value of table 2 (code &amp; amounts)?</p>
  
  <p>I've also thought about adding a blank row in the shorter table every time a code is different in order to create a correspondence in table length (as the two tables are sorted by the values in corresponding sheets when users copy/paste data). It fails every time a "new code" is detected (or not found or not present):</p>
  
  <pre><code>Dim Arr() As Variant
  ReDim Arr(1 To 30)
  
  NonVideAddLondon = Worksheets("Paste Transcom").Range("A" &amp; Rows.Count).End(xlUp).Row
  
  
  For i = 2 To NonVideAddLondon
  
  If Cells(i, "B") = "" Then
  Arr(i) = Cells(i, "B").Row
  Worksheets("Paste Investec").Rows(Arr(i)).Insert shift:=xlDown
  Else
  End If
  
  Next i
  </code></pre>