How to Merge Two tables in excel

[MShahid777]

[FONT=&quot]I have two tables(Tabl_A and Table_B) in Excel as shown in the following figure. I want to merge them so that output is like the third table (Tabl_C). How can I do this??[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]Table_A[/FONT]

NumberOfSection	Control_Set
1	2
2	6
3	15

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>

[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]Table_B[/FONT]
[FONT=&quot]​[/FONT]

NumberOfSections	Zoo_Set
1	3
3	43
4	22

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>

[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]Table_C[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]

NumberOfSections	Control_Set	Zoo_Set
1	2	3
2	6
3	15	43
4		22

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>

 

[Fluff]

Cross posted
https://www.excelforum.com/excel-gen...ml#post4967492
https://www.excelguru.ca/forums/show...ables-in-excel
http://www.msofficeforums.com/excel/...les-excel.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[jpvh8]

Assuming the name of the first column in your example is a typo and it should instead match the other two table:

Sub MergeTables()
Dim aCn As Object, aRs As Object, sql As String, t1 As Range, t2 As Range, n As Long, lo As ListObject, r3 As Range
  Set aCn = CreateObject("ADODB.Connection")
  Set aRs = CreateObject("ADODB.Recordset")
  With ThisWorkbook
    Set t1 = .Sheets(1).ListObjects("Table_A").Range ' edit to suit, e.g., what worksheet is this table on?
    Set t2 = .Sheets(2).ListObjects("Table_B").Range ' edit to suit, e.g., what worksheet is this table on?
    On Error Resume Next
    Set lo = .Sheets(3).ListObjects("Table_C") ' edit to suit, e.g., what worksheet is this table on?
    On Error GoTo 0
    If Not lo Is Nothing Then
      Set r3 = lo.Range(1)
      lo.Delete
    Else: Set r3 = .Sheets(3).Range("A1") ' if Table_C doesn't exist, where should it be created?
    End If
  End With
  With aCn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & ThisWorkbook.Name & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    .Open ' if this line errors, install the ACE driver from https://www.microsoft.com/en-us/download/details.aspx?id=23734
  End With
  ' Below "SQL" is complicated due to the ADO driver's lack of support for FULL OUTER JOIN
  sql = "SELECT t1.NumberOfSections, t1.Control_Set, t2.Zoo_Set FROM [" & t1.Parent.Name & "$" & t1.Address(False, False) & _
      "] AS t1 LEFT OUTER JOIN [" & t2.Parent.Name & "$" & t2.Address(False, False) & "] AS t2 ON t1.NumberOfSection = t2.NumberOfSections " & _
      "UNION SELECT t2.NumberOfSections, t1.Control_Set, t2.Zoo_Set FROM [" & t1.Parent.Name & "$" & t1.Address(False, False) & _
      "] AS t1 RIGHT OUTER JOIN [" & t2.Parent.Name & "$" & t2.Address(False, False) & "] AS t2 ON t1.NumberOfSection = t2.NumberOfSections"
  aRs.Open sql, aCn, 3, 1, 1
  With r3
    For n = 1 To aRs.Fields.Count ' create table header row
      .Cells(, n) = aRs(n - 1).Name
    Next
    .Cells(2).CopyFromRecordset aRs
    .Parent.ListObjects.Add(xlSrcRange, .CurrentRegion, , xlYes).Name = "Table_C"
  End With
  aCn.Close
  Set aCn = Nothing
  Set aRs = Nothing
End Sub

 

[sandy666]

with PowerQuery

NumberOfSection	Control_Set		NumberOfSection	Zoo_Set		NumberOfSection	Control_Set	Zoo_Set
1​	2​		1​	3​		1​	2​	3​
2​	6​		3​	43​		2​	6​
3​	15​		4​	22​		3​	15​	43​
						4​		22​

[SIZE=1]let
    Source = Table.Combine({Table1, Table2}),
    #"Grouped Rows" = Table.Group(Source, {"NumberOfSection"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Control_Set", each Table.Column([Count],"Control_Set")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Control_Set", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Zoo_Set", each Table.Column([Count],"Zoo_Set")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Zoo_Set", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Count"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Control_Set", Int64.Type}, {"Zoo_Set", Int64.Type}})
in
    #"Changed Type"[/SIZE]