join two table

[msniha]

I have two excel table structure and order

structure contain

Product | Component | Order_Quantity
A | C1 | 1
A | C2 | 3
A | C3 | 3
B | C1 | 1
B | C4 | 2
C | C1 | 1
D | C2 | 3
C | C3 | 3
order contain

Order_n | Product | Quantity
1 | A | 10
2 | B | 10
3 | A | 10
4 | C | 10
6 | B | 10
How can i join the two table in excel 2013 (without power pivot or another add-in) to see result like this :

Order_n | Product | Order_Qty | component | Quantity | Total_QTY
1 | A | 10 | C1 | 1 | 10
1 | A | 10 | C2 | 3 | 30
1 | A | 10 | C3 | 3 | 30
2 | B | 10 | C1 | 1 | 10
2 | B | 10 | C4 | 2 | 20
3 | A | 10 | C1 | 1 | 10
3 | A | 10 | C2 | 3 | 30
3 | A | 10 | C3 | 3 | 30
4 | C | 10 | C1 | 1 | 10
4 | C | 10 | C3 | 3 | 30
6 | B | 10 | C1 | 1 | 10
6 | B | 10 | C4 | 2 | 20

[Rizky]

Could you please upload your sample workbook, to avoid retyping again, Click "Go Advanced" button and find Paperclip button to attach the file

Thanks

[Bob Phillips]

Public Sub JoinData()
Dim t1 As Worksheet
Dim t2 As Worksheet
Dim matchrow1 As Long
Dim numrows1 As Long
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim lastrow3 As Long
Dim i As Long


   Application.ScreenUpdating = False
   
   Set t1 = Worksheets("Sheet1")
   With t1
    
        lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
   Set t2 = Worksheets("Sheet2")
   With t2
    
        lastrow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    With ActiveSheet
    
        .Range("A1:F1").Value = Array("Order_n", "Product", "Order_Qty", "component", "Quantity", "Total_QTY")
        t2.Range("A2").Resize(lastrow2 - 1, 3).Copy .Range("A2")
        For i = lastrow2 To 2 Step -1
        
            matchrow1 = 0
            On Error Resume Next
            matchrow1 = Application.Match(.Cells(i, "B").Value, t1.Columns(1), 0)
            On Error GoTo 0
            If matchrow1 > 0 Then
            
               numrows1 = Application.CountIf(t1.Columns(1), .Cells(i, "B").Value)
               .Rows(i + 1).Resize(numrows1 - 1).Insert
               t1.Cells(matchrow1, "B").Resize(numrows1, 2).Copy .Cells(i, "D")
               .Cells(i, "A").Resize(, 3).Copy .Cells(i + 1, "A").Resize(numrows1 - 1)
            End If
        Next i
        
        lastrow3 = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("F2").Resize(lastrow3 - 1).FormulaR1C1 = "=RC[-3]*RC[-1]"
    End With


   Application.ScreenUpdating = True
End Sub

[msniha]

Public Sub JoinData()
Dim t1 As Worksheet
Dim t2 As Worksheet
Dim matchrow1 As Long
Dim numrows1 As Long
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim lastrow3 As Long
Dim i As Long


   Application.ScreenUpdating = False
   
   Set t1 = Worksheets("Sheet1")
   With t1
    
        lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
   Set t2 = Worksheets("Sheet2")
   With t2
    
        lastrow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    Worksheets.Add after:=Worksheets(Worksheets.Count)
    With ActiveSheet
    
        .Range("A1:F1").Value = Array("Order_n", "Product", "Order_Qty", "component", "Quantity", "Total_QTY")
        t2.Range("A2").Resize(lastrow2 - 1, 3).Copy .Range("A2")
        For i = lastrow2 To 2 Step -1
        
            matchrow1 = 0
            On Error Resume Next
            matchrow1 = Application.Match(.Cells(i, "B").Value, t1.Columns(1), 0)
            On Error GoTo 0
            If matchrow1 > 0 Then
            
               numrows1 = Application.CountIf(t1.Columns(1), .Cells(i, "B").Value)
               .Rows(i + 1).Resize(numrows1 - 1).Insert
               t1.Cells(matchrow1, "B").Resize(numrows1, 2).Copy .Cells(i, "D")
               .Cells(i, "A").Resize(, 3).Copy .Cells(i + 1, "A").Resize(numrows1 - 1)
            End If
        Next i
        
        lastrow3 = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("F2").Resize(lastrow3 - 1).FormulaR1C1 = "=RC[-3]*RC[-1]"
    End With


   Application.ScreenUpdating = True
End Sub

Thanks, but I need a native way to do that :/

[Bob Phillips]

What the heck does native way mean?

[msniha]

What the heck does native way mean?

Without the use of VBA (macro)

[Bob Phillips]

Well, good luck with that.

[Mike_Alex]

If you do not want a table that will be joined, you could put in a bunch of sumif's.