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!

    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?




  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    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 (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/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
    Code:
    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
  •