Multiple returns in-line

**GonzoSS** · 05-09-2012, 10:01 AM

Not quite it, but thanks GeneralDisarray.

I have modified the attachment to show the result I am looking for.
The purpose of the formula is to automatically track the "Action ID" numbers assigned to items.
e.g. Pumps are a problem(Item) - I assign actions to fix the item (A01, A02). When the actions have been entered into the Action Register they automatically appear in the Item Register (as shown in the attachment). So one item may have many actions assigned to it, this is just a way of tracking them.

I hope that makes sense.

Thanks.

HSE Register2.xlsx

**GeneralDisarray** · 05-09-2012, 11:17 AM

Attachment demos this solution -- NOTE you will need to enable macro content in order for the UDF (user-defined-function) to work.

The code below will help you remember what to enter into the function and in what order: Look in the I column to see how this UDF was implemented in the worksheet.


Option Explicit

Public Function getAllMatching(ValueToMatch As String, MatchColumn As Range, ReturnOffset As Integer) As String
    Dim i As Long
    Dim cell As Range
    Dim values() As String
    ReDim values(1 To 1)
    
    For Each cell In MatchColumn
        If cell.Value = ValueToMatch Then
            ReDim Preserve values(1 To UBound(values) + 1)
            values(UBound(values)) = cell.Offset(0, ReturnOffset).Value
        End If
    Next cell
    
    On Error GoTo endfunction
    For i = 2 To UBound(values)
        getAllMatching = getAllMatching & "," & values(i)
    Next i
    'trim lead comma'
        getAllMatching = Right(getAllMatching, Len(getAllMatching) - 1)
    Exit Function
endfunction:
    
End Function