Results 1 to 3 of 3

Thread: need formula for getting multiple search values

  1. #1

    need formula for getting multiple search values

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


    hi all,

    Please find attached the excel sheet for reference.
    • 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.

      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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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

Posting Permissions

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