I have the below data in a spreadsheet, columns A-D:
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
I pass "Red" to the function and I get returned:
"Car" and "Truck".
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.
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)
ReDim Preserve newArr(1 To j)
Let foo2 = Join$(newArr, ", ")
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?
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.