Array to put results in one column in to one cell

This formula

=concatall(IF($C$3:$C$22=G2,IF($D$3:$D$22<>"",$D$3:$D$22,""),""),"")

array entered, along with this code in a module:

Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
   
    Dim DataIndex As Variant
    Dim strResult As String
    
    If IsArray(varData) _
    Or TypeOf varData Is Range _
    Or TypeOf varData Is Collection Then
        
        For Each DataIndex In varData
            If Len(DataIndex) > 0 Then
                 If bUnique = True Then
                      If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
                       trResult = strResult & "||" & DataIndex
                    End If
                Else
                    strResult = strResult & "||" & DataIndex
                End If
            End If
        Next DataIndex
        
        strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
        
    Else
        strResult = varData
    End If
    
    ConcatAll = strResult
    
End Function

does that.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Remember to enable macros on opening...

Nope. forget all that. use this array formula instead: No need for VBA. It gives the results concatenated and in ascending order.

=CONCATENATE(IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),1),""),IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),2),""),IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),3),""),IFERROR(SMALL(IF(($C$3:$C$22=G2)*($D$3:$D$22> 0),$D$3:$D$22),4),""),"")

You're welcome and thanks for the rep...