Merge or Append

coding123456

New Member
Joined
Feb 11, 2025
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a 2 Data Table that have the very same column structure. Is just the Row some are different from each others

Example
Table 1
Name 1Descriptionb/f
A12399
B1234100

Table 2
Name 1Descriptionc/f
A12399

I want the results to be the below. How do it do it?

Result
Name 1Descriptionb/fc/f
A1239999
B1234100
 
I have a 2 Data Table that have the very same column structure. Is just the Row some are different from each others

Example
Table 1
Name 1Descriptionb/f
A12399
B1234100

Table 2
Name 1Descriptionc/f
A12399

I want the results to be the below. How do it do it?

Result
Name 1Descriptionb/fc/f
A1239999
B1234100
How many different values do you have in the third column?

Is this a one off exercise or something that you want to do on a regular basis?

Is it possible to just have one table instead of two in the future?
 
Upvote 0
How many different values do you have in the third column?
My third column is depending on my "Name1".

Is this a one off exercise or something that you want to do on a regular basis?
Once a year exercise.

Is it possible to just have one table instead of two in the future?
No as it is run by a system. Is a report that the parameter is based by year that i have no flexibility.
 
Upvote 0
I have a 2 Data Table that have the very same column structure. Is just the Row some are different from each others

Example
Table 1
Name 1Descriptionb/f
A12399
B1234100

Table 2
Name 1Descriptionc/f
A12399

I want the results to be the below. How do it do it?

Result
Name 1Descriptionb/fc/f
A1239999
B1234100
Try this.

Follow the instructions on line one of the code.

It puts the results in a sheet named 'MergedData'. Make sure that you don't already have a sheet with this name.

VBA Code:
Public Sub subMergeTables()
Dim arr() As Variant
Dim objTable1 As Object
Dim objTable2 As Object
Dim Q As String
Dim WsResults As Worksheet
Dim WsMerged As Worksheet
Dim objMerged As ListObject
  
  ' **** Change the table names in the next two lines as appropriate. ****
  Set objTable1 = Application.Range("tblOne").ListObject
  Set objTable2 = Application.Range("tblTwo").ListObject
  
  Call subCreateSheet("MergedData")
  
  Set WsMerged = Worksheets("MergedData")
  
  WsMerged.Activate
  
  Q = Chr(34)
  
  With WsMerged
    
    .Range("A1").Resize(1, 4).Value = Array("Name 1", "Description", "b/f", "c/f")
    
    .Range("A2").Formula2 = "=LET(d,VSTACK(HSTACK(" & objTable1.Name & _
      ",TRANSPOSE(TEXTSPLIT(REPT(" & Q & "b/f," & Q & ",ROWS(" & objTable1.Name & "))," _
      & Q & "," & Q & ",,TRUE))),HSTACK(" & objTable2.Name & ",TRANSPOSE(TEXTSPLIT(REPT(" & _
      Q & "c/f," & Q & ",ROWS(" & objTable2.Name & "))," & Q & "," & Q & ",,TRUE)))),d)"

    With .Range("A1").CurrentRegion
      .Value = .Value
    End With
  
    Set objMerged = .ListObjects.Add(xlSrcRange, WsMerged.Range("A1").CurrentRegion, , xlYes)
  
    .ListObjects(1).Name = "tblMergedData"
    
    .Range("A1").AutoFilter
    
    .Range("F1").Resize(1, 4).Value = Array("Name 1", "Description", "b/f", "c/f")
    
    arr = Evaluate("=UNIQUE(VSTACK(CHOOSECOLS(tblOne,{1,2}),CHOOSECOLS(tblTwo,{1,2})))")
  
    .Range("F2").Resize(UBound(arr), 2).Value = arr
    
    .Range("H2").Formula2 = "=LET(s,SUMIFS(tblMergedData[b/f],tblMergedData[Name 1],F2:F" & _
      UBound(arr) + 1 & ",tblMergedData[Description],G2:G" & UBound(arr) + 1 & _
      ",tblMergedData[c/f],H1),IF(s>0,s," & Q & Q & "))"
  
    .Range("I2").Formula2 = "=LET(s,SUMIFS(tblMergedData[b/f],tblMergedData[Name 1],F2:F" & _
      UBound(arr) + 1 & ",tblMergedData[Description],G2:G" & UBound(arr) + 1 & _
      ",tblMergedData[c/f],I1),IF(s>0,s," & Q & Q & "))"
  
    With .Range("F1").CurrentRegion
      .Value = .Value
    End With
 
    With .Cells
      .RowHeight = 24
      .Font.Size = 14
      .Font.Name = "Arial"
      .HorizontalAlignment = xlLeft
      .VerticalAlignment = xlCenter
      .IndentLevel = 1
      .EntireColumn.AutoFit
    End With
          
  End With
  
  MsgBox "Tables merged and results calculated.", vbOKOnly, "Confirmation"
  
End Sub

Private Sub subCreateSheet(ByVal strSheet As String)
Dim WsActive As Worksheet

  Set WsActive = ActiveSheet

  If Evaluate("isref('" & strSheet & "'!A1)") Then
     Worksheets(strSheet).Cells.Clear
  Else
     Worksheets.Add after:=Sheets(Sheets.Count)
     ActiveSheet.Name = strSheet
  End If
  
  WsActive.Activate

End Sub
 
Upvote 0
I used Power Query and merged the two tables with a full outer join

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(Source, {"Name 1"}, Source2, {"Name 1"}, "Tbl", JoinKind.FullOuter),
    #"Expanded Tbl" = Table.ExpandTableColumn(MQ, "Tbl", {"c/f"}, {"c/f"})

in
    #"Expanded Tbl"
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

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