How to Merge Two tables in excel

M

MShahid777

New Member
Joined
Sep 2, 2018
Messages
1
[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]
[TABLE="class: s570a4-19 MzlEq"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: s570a4-25 hWWGpP, align: center"]NumberOfSection[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"]Control_Set[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"][/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]1[/TD]
[TD="class: s570a4-21 eUFXof"]2[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]2[/TD]
[TD="class: s570a4-21 eUFXof"]6[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]3[/TD]
[TD="class: s570a4-21 eUFXof"]15[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]Table_B[/FONT]
[FONT=&quot]​[/FONT]
[TABLE="class: s570a4-19 MzlEq"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: s570a4-25 hWWGpP, align: center"]NumberOfSections[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"]Zoo_Set[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"][/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]1[/TD]
[TD="class: s570a4-21 eUFXof"]3[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]3[/TD]
[TD="class: s570a4-21 eUFXof"]43[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]4[/TD]
[TD="class: s570a4-21 eUFXof"]22[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]Table_C[/FONT]
[FONT=&quot]​[/FONT]
[FONT=&quot]​[/FONT]
[TABLE="class: s570a4-19 MzlEq"]
<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TH="class: s570a4-25 hWWGpP, align: center"]NumberOfSections[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"]Control_Set[/TH]
[TH="class: s570a4-25 hWWGpP, align: center"]Zoo_Set[/TH]
[/TR]
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]1[/TD]
[TD="class: s570a4-21 eUFXof"]2[/TD]
[TD="class: s570a4-21 eUFXof"]3[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]2[/TD]
[TD="class: s570a4-21 eUFXof"]6[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]3[/TD]
[TD="class: s570a4-21 eUFXof"]15[/TD]
[TD="class: s570a4-21 eUFXof"]43[/TD]
[/TR]
[TR="class: s570a4-20 bKSoXW, bgcolor: rgba(255, 255, 255, 0.8)"]
[TD="class: s570a4-21 eUFXof"]4[/TD]
[TD="class: s570a4-21 eUFXof"][/TD]
[TD="class: s570a4-21 eUFXof"]22[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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.
 
Upvote 0
Assuming the name of the first column in your example is a typo and it should instead match the other two table:
Code: 
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
 
Upvote 0
with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]NumberOfSection[/td][td=bgcolor:#5B9BD5]Control_Set[/td][td][/td][td=bgcolor:#5B9BD5]NumberOfSection[/td][td=bgcolor:#5B9BD5]Zoo_Set[/td][td][/td][td=bgcolor:#70AD47]NumberOfSection[/td][td=bgcolor:#70AD47]Control_Set[/td][td=bgcolor:#70AD47]Zoo_Set[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
6​
[/td][td][/td][td]
3​
[/td][td]
43​
[/td][td][/td][td]
2​
[/td][td]
6​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
15​
[/td][td][/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
22​
[/td][td][/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
15​
[/td][td=bgcolor:#E2EFDA]
43​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
4​
[/td][td][/td][td]
22​
[/td][/tr]
[/table]

Code: 
[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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
R
  • Question Question
Random
Replies
1
Views
582
Fluff
Fluff
kelly mort
  • Question Question
Trying to use "PageSetup.PaperSize" with a constant not working
Replies
6
Views
2K
kelly mort
kelly mort
I
  • Question Question
Copy files from one folder to another based on a list in Excel
Replies
1
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top