Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Lookup array of values

    Hi

    I have the below data in a spreadsheet, columns A-D:
    Code:
    A       B         C        D
    Red     Foo       1        Car
    Blue    Foo       2        Truck
    Purple  Foo       3        Lorry
    Red     Bar       4        Truck
    Blue    Bar       5        Moped
    Purple  Bar       6        Car
    Using VBA (or a worksheet function if required) I would like to pass in the look for the corresponding Value(s) in Column D based on a value found in column A
    e.g.
    I pass "Red" to the function and I get returned:
    "Car" and "Truck".

    Essentially I am after the equivalent of:
    Code:
    SELECT D 
    FROM theRange 
    WHERE A = "Car"
    Many thanks
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I suppose what I want is something like VLOOKUP but that returns an array rather than a scalar.
    Last edited by pootle flump; 10-21-09 at 13:50.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    The bottom line is that Excel's built-in worksheet functions don't do this well. It's possible to do but it's not pretty (I'll happily demonstrate a native formula solution if you are interested), so a VBA function is a very reasonable choice.

    Nate Oliver is a MS Excel MVP and he put this one together, so I would expect it to be pretty solid:
    http://www.mrexcel.com/forum/showpos...3&postcount=13

    Code:
    Public Function foo2( _
        ByRef rngIn As Range, ByRef nameIn As String) As String
    Dim tmpArr() As Variant, newArr() As String
    Dim i As Long, j As Long
    Let tmpArr = rngIn.Value
    ReDim newArr(1 To UBound(tmpArr, 1))
    For i = LBound(tmpArr, 1) To UBound(tmpArr, 1)
        If tmpArr(i, 1) = nameIn Then
            Let j = j + 1
            Let newArr(j) = tmpArr(i, 2)
        End If
    Next
    ReDim Preserve newArr(1 To j)
    Let foo2 = Join$(newArr, ", ")
    End Function

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks again Colin

    Yeah that looks pretty logical way to do things. I'll try it tomorrow.

    BTW - dunno if you would know this but - I would typically redim preserve the array on each pass of the loop, so the array would never be larger than needed at that moment in time. Looking at this code, that constant resizing might be less efficient than what this guy does (initialises the array to the maximum possible size and then "shrinks-to-fit" at the end). Do you know best practice on this?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi pootle flump,

    I think that's deliberate on his part. As you said, ReDim Preserve is quite an expensive operation so, since you could be looping through thousands of records, it makes sense to only do it at the start and end.

    Depending on the big picture (such as user interaction) and if you intend to return the results to a worksheet, another option might be to automate the AdvancedFilter to give you the results. Crude example attached.
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Colin

    I had seen some stuff about advanced filter but rejected it. The array is only used in code for some calculations
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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