need formula for getting multiple search values

[aditi]

556

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
]