Vba or method to compare two tables and rows

[dtms1]

So I have two large tables on two separate sheets that I am trying to compare.
On both tables, 1 column have a unique value (never repeat)

What I'm trying to do is do a VBA macro to compare a column on one table against a cilumn on the other table and identify where a unique value exsist in the first table that is not on the 2nd table.
Once identified, all the rows of that unique value found will be inserted into the table located on sheet2

So for example:

On Sheet 1 I have:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]comparing[/TD]
[TD="width: 64"]info1[/TD]
[TD="width: 64"]info2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD]pear[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]yellow[/TD]
[TD]berries[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]black[/TD]
[TD]we[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]orange[/TD]
[TD]orange[/TD]
[/TR]
</tbody>[/TABLE]
(this table has a name called "SourceTable")


On Sheet 2 I have:
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 256"]
<tbody>[TR]
[TD]comparing
[/TD]
[TD]info1
[/TD]
[TD]info2
[/TD]
[TD]Notes
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]yellow
[/TD]
[TD]berries
[/TD]
[TD]good
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]red
[/TD]
[TD]apple
[/TD]
[TD]good
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]blue
[/TD]
[TD]pear
[/TD]
[TD]bad
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
(just a note, the table on sheet1 pulls from a SQL query database and rows can be added upon refresh of the data. When that happens is all adjacent columns that were added on excel (for example the Notes column) get misaligned because there is no relationship between the manual column added (notes column) and the SQL table (unless its copied over to a 2nd table like what this is trying to do here)


End result would look like:
[TABLE="width: 256"]
<tbody>[TR]
[TD]comparing
[/TD]
[TD]info1
[/TD]
[TD]info2
[/TD]
[TD]Notes
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]yellow
[/TD]
[TD]berries
[/TD]
[TD]good
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]red
[/TD]
[TD]apple
[/TD]
[TD]good
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]blue
[/TD]
[TD]pear
[/TD]
[TD]bad
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]black
[/TD]
[TD]we
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]orange
[/TD]
[TD]orange
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
(not sure what happen to the boarders, but not relevant)



Did that kind of make sense?
Any ideas?

thanks

 

[Fazza]

Sub one_way()


  'http://www.mrexcel.com/forum/excel-questions/862509-visual-basic-applications-method-compare-two-tables-rows.html


  Dim strConn As String
  Dim strSQL As String
  Dim objConn As Object


  'The connection string is specific to the Excel version & file type. This string is Excel 2003
  'Change it to suit. Refer http://www.connectionstrings.com/
  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)


  strSQL = Join$(Array( _
    "INSERT INTO [Sheet2$] IN '" & ActiveWorkbook.FullName & "' 'Excel 8.0;'", _
    "SELECT *", _
    "FROM (", _
    "SELECT Sh1.*", _
    "FROM [Sheet1$] Sh1 LEFT OUTER JOIN [Sheet2$] Sh2 ON Sh1.comparing = Sh2.comparing", _
    "WHERE Sh2.info1 IS NULL)"), vbCr)
  
  Set objConn = CreateObject("ADODB.Connection")
  With objConn
    .Open strConn
    .Execute strSQL
    .Close
  End With
  Set objConn = Nothing
  
End Sub

 

[Fazza]

or same idea except using a recordset. just a couple of lines different

Sub or_using_recordset()

  'http://www.mrexcel.com/forum/excel-questions/862509-visual-basic-applications-method-compare-two-tables-rows.html

  Dim strConn As String
  Dim strSQL As String
  Dim objRS As Object

  'The connection string is specific to the Excel version & file type. This string is Excel 2003
  'Change it to suit. Refer http://www.connectionstrings.com/
  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

  strSQL = Join$(Array( _
    "INSERT INTO [Sheet2$] IN '" & ActiveWorkbook.FullName & "' 'Excel 8.0;'", _
    "SELECT *", _
    "FROM (", _
    "SELECT Sh1.*", _
    "FROM [Sheet1$] Sh1 LEFT OUTER JOIN [Sheet2$] Sh2 ON Sh1.comparing = Sh2.comparing", _
    "WHERE Sh2.info1 IS NULL)"), vbCr)
  
  Set objRS = CreateObject("ADODB.Recordset")
  objRS.Open strSQL, strConn
  Set objRS = Nothing
  
End Sub