Results 1 to 8 of 8

Thread: join two table

  1. #1

    join two table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

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

    Thanks

  3. #3
    Code:
    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

  4. #4
    Quote Originally Posted by Bob Phillips View Post
    Code:
    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 :/

  5. #5
    What the heck does native way mean?

  6. #6
    Quote Originally Posted by Bob Phillips View Post
    What the heck does native way mean?
    Without the use of VBA (macro)

  7. #7
    Well, good luck with that.

  8. #8
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    If you do not want a table that will be joined, you could put in a bunch of sumif's.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •