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 Zand 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

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)
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.