Join two excel 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

 

[Fazza]

hi,

I'm not familiar with Excel 2013 though assume it is like all previous versions for which once you've saved the file go ALT-D-D-N and follow the wizard. Select your data file & tables, join on the common Product field and within MS Query add the extra field (by editing the SQL in the GUI) Total_Qty as the product of two fields. Sort as required. Note, I think just a simple mix up, the Order_Quantity and Quantity fields have swapped between the sample inputs & sample output.

regards

 

[johnmpl]

Hi!

If you have:

The Structure Contain in Range A1:C9
The Order Contain in Range E1:G6

Run this macro (you will obtain the results in J1):

Sub JoinTable()
    Dim R1 As Range, R2 As Range, C As Range, F As Range
    Dim FiAdd$, A&, M
    
    Set R1 = Range(Range("A2"), Range("A1").End(xlDown))
    Set R2 = Range(Range("E2"), Range("E1").End(xlDown))
    ReDim M(1 To 6, 0 To 0)
    
    For Each C In R2
        Set F = R1.Find(C.Offset(, 1), LookIn:=xlValues)
        If Not F Is Nothing Then
            FiAdd = F.Address
            Do
                A = A + 1
                ReDim Preserve M(1 To 6, 1 To A)
                M(1, A) = C
                M(2, A) = C.Offset(, 1)
                M(3, A) = C.Offset(, 2)
                M(4, A) = F.Offset(, 1)
                M(5, A) = F.Offset(, 2)
                M(6, A) = F.Offset(, 2) * C.Offset(, 2)
                Set F = R1.FindNext(F)
            Loop While Not F Is Nothing And F.Address <> FiAdd
        End If
    Next C
    
    With Range("J1")
        .CurrentRegion.ClearContents
        .Resize(, 6) = Array([E1], [F1], "Order Qty", [B1], [G1], "Total Qty")
        .Offset(1).Resize(A, 6) = Application.Transpose(M)
        .CurrentRegion.EntireColumn.AutoFit
    End With
    Set R1 = Nothing: Set R2 = Nothing: Set C = Nothing
    Set F = Nothing: Erase M
End Sub

I Hope it Helps! Please Comment! Blessings!