need formula for getting multiple search values

[aditi]

Excel_issue.xlsx

hi all,

Please find attached the excel sheet for reference.
Problem:

• In Tab 'Sheet 1':
  We have supplier- Customer Relationship. Each Supplier can Supply products to multiple customers.
  
  In Sheet 2:
  We have list of the products that each supplier can supply. Each supplier can supply multiple products.
  
  Result:
  Against all the supplier -customer combinations in Sheet1.We want to include the products from sheet2, as they are supplied by the supplier.
  So if any supplier is supplying 3 products, then there should be 3 lines for that supplier - customer combination in Sheet1. With different products in each line (as shown in result sheet).
  And this should happen every time that supplier is there.
  
  
  Can anyone please help me, how can we get this data?
  
  
  
  

[Ken Puls]

Hi there,

Sorry for the late reply on this.

What version of Excel are you using?

If you're using 2007, I could whip up a macro to build a table out of this.

If you're using 2010 though, we could build you a PowerPivot solution for the issue, which would probably be the better route. (You'd need to install PowerPivot, but it's a free download.)

[Bob Phillips]

Sub CreateSet()
Dim target As Worksheet
Dim sh2 As Worksheet
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim nextrow As Long
Dim i As Long, j As Long
    
    Application.ScreenUpdating = False


    Set target = Worksheets("result")
    target.Range("A1:C1").Value = Array("Supplier", "Product", "Customer")
    
    Set sh2 = Worksheets("Sheet2")
    With sh2
    
        lastrow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    With Worksheets("Sheet1")
    
        lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        nextrow = 1
        For i = 2 To lastrow1
        
            For j = 2 To lastrow2
            
                If sh2.Cells(j, "A").Value = .Cells(i, "A").Value Then
                    
                    nextrow = nextrow + 1
                
                    target.Cells(nextrow, "A").Value = .Cells(i, "A").Value
                    target.Cells(nextrow, "B").Value = sh2.Cells(j, "B").Value
                    target.Cells(nextrow, "C").Value = .Cells(i, "B").Value
                End If
            Next j
            
        Next i
    End With
    
    Application.ScreenUpdating = True


End Sub
]