Consulting

Results 1 to 4 of 4

Thread: Return Multiple Values based on Lookup Value

  1. #1
    VBAX Regular
    Joined
    Sep 2011
    Posts
    42
    Location

    Return Multiple Values based on Lookup Value

    Hi all,

    I was wondering if someone could help me to return multiple numbers based on a lookup value. I have a unique SKU (column F), but there are multiple purchase order (PO) numbers associated with it (column J).

    What I am trying to get is, based on the SKU number, it would give me all the PO’s related to it in one cell (I.e. see cells F17:G18). The values can be returned in one cell together, or over multiple columns. Is this possible? Right now I put the data into a pivot table, one column has the SKU, and another one has multiple POs, but then I have to transpose the data manually.

    Thank you in advance.
    Attached Files Attached Files
    Last edited by jaydee; 01-23-2014 at 07:22 PM.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,953
    Location
    I'm fond of using User Defined Functions for things like this, but some of the worksheet formula experts might have a non-VBA way

    Option Explicit
    'Usage: =POandSKU(F11,$F:$F,$J:$J)
    Function POandSKU(SKU As String, RangeSKU As Range, RangePO As Range) As String
        Dim s As String
        Dim r As Range
        
        For Each r In Intersect(RangeSKU.Columns(1), RangeSKU.Parent.UsedRange).Cells
            If r.Value = SKU Then
                s = s & Intersect(r.EntireRow, RangePO.Columns(1)).Value & ", "
            End If
        Next
        
        If Len(s) > 0 Then s = Left(s, Len(s) - 2)
    
        POandSKU = s
    End Function

    Paul
    Attached Files Attached Files

  3. #3
    VBAX Regular
    Joined
    Sep 2011
    Posts
    42
    Location
    Thank you, Paul!
    You are an evil genius ^_^

    This works good, it saves me hours of work of doing it manually.

    Thank you again!!

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,953
    Location
    Not a problem

    It could always use more error checking (what doesn't)

    Paul
    Last edited by Paul_Hossler; 01-25-2014 at 10:46 AM. Reason: Pasted into wrong thread :-(

Posting Permissions

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