# Merge multiple tables

I have several tables like this:

``````Table A                               Table B
Col Aa    Col Ba     Col Ca         Col Ab   Col Bb   Col Cb   Col Db
1        Ab1         Ac1            1        Bb1      Bc1      Bd1
2        Ab2         Ac2            3        Bb3      Bc3      Bd3
4        Ab4         Ac4            3        Bb3'     Bc3'     Bd3'
``````

And I would like to have a resulting table like that

``````Col X       Col Y         Col Z
1          Ab1           Ac1
2          Ab2           Ac2
4          Ab4           Ac4
1          Bb1           Bd1
3          Bb3           Bd3'
3          Bb3'          Bd3'
``````

It's basically adding all the rows of multiple tables, but manually deciding what columns in each table should be added, and deciding what column from table A goes with what column of table B, and in what column of the final table (and what columns not to add)

I guess I could just do copy paste but I would prefer not to. I could also change the name columns in each table if necessary. `Col Ba = Col Y`, `Col Ca = Col Z`, `Col Db = Col Z`and so on, but again I would prefer not to.

I'm using Excel 2016. Using relationships doesn't work because apparently, data has to be unique in each column (no duplicates)

• But col X has duplicates and you need a condition or key or relation between tables to know what and how to merge – yass Mar 27 '17 at 22:38
• It cannot be done then? I guess I could create a dummy column in each table as an ID for the relationship. – Daniel Mar 28 '17 at 6:46
• You need a common column between your Tables and at least one condition in order to create a formula to copy the other information – yass Mar 28 '17 at 12:13
• Let's say I do have a column table. How do I do it? – Daniel Mar 28 '17 at 20:58
• You can use Vlookup or Index/Match you have to precise it in order to write the formula, you have to explain – yass Mar 28 '17 at 21:11

## 1 Answer

You can use this code for Merge Data Ranges when has the same structure.

``````Sub MergeData()

Dim i As Integer
Dim xTCount As Variant
Dim xWs As Worksheet
On Error Resume Next LInput:
xTCount = Application.InputBox( "The number of title rows" , "", "1" )

If TypeName(xTCount) = "Boolean" Then Exit Sub
If Not IsNumeric(xTCount) Then
MsgBox "Only can enter number"
GoTo LInput
End If
Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
xWs.Name = "Merge Data"

Worksheets(2).Range( "A1" ).EntireRow.Copy Destination:=xWs.Range( "A1" )

For i = 2 To Worksheets.Count
Worksheets(i).Range( "A1" ).CurrentRegion.Offset( CInt(xTCount), 0).Copy _
Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
Next
End Sub
``````
• Sorry but although I am familiar with C I don't fully understand what the code is doing. I guess I would need to understandt it to adapt it to my tables. – Daniel Mar 28 '17 at 21:12