CONCAT into one cell based on 2 values

[Admin]

Hi

Welcome to ExcelFox !!!

Try this UDF.

and use like:

=CONCATIFS(C1:C30,", ",A1:A30,E2,B1:B30,F2)

where C1:C30 - concatenate range
", " - delimiter
A1:A30 - First Criteria range
E2 - First criteria
B1:B30 - second criteria range
F2 - second criteria

Option Explicit

Function CONCATIFS(ByVal ConcatCol As Variant, ByVal Delim As String, ParamArray ParamA() As Variant) As Variant
    
    '//ParamA=each pair should be Criteria range followed by it's criteria
    
    Dim lngLoopC        As Long
    Dim lngLoopR        As Long
    Dim lngLoop         As Long
    Dim lngCount        As Long
    Dim lngCounter      As Long
    Dim lngIndex        As Long
    Dim lngCase         As Long
    Dim varOP()         As Variant
    Dim strMatch        As String
    Dim blnTranspose    As Boolean
    
    If TypeOf ConcatCol Is Range Then
        If ConcatCol.Columns.Count > 1 And ConcatCol.Rows.Count = 1 Then
            blnTranspose = True
            ConcatCol = Application.Transpose(Application.Transpose(ConcatCol.Value2))
        ElseIf ConcatCol.Columns.Count = 1 And ConcatCol.Rows.Count > 1 Then
            ConcatCol = Application.Transpose(ConcatCol.Value2)
        End If
    End If
    
    For lngLoop = LBound(ParamA) To UBound(ParamA)
        If TypeOf ParamA(lngLoop) Is Range Then
            If blnTranspose Then
                ParamA(lngLoop) = Application.Transpose(Application.Transpose(ParamA(lngLoop).Value2))
            Else
                ParamA(lngLoop) = Application.Transpose(ParamA(lngLoop).Value2)
            End If
        End If
    Next
    
    ReDim varOP(1 To UBound(ConcatCol))
    lngCount = (1 + UBound(ParamA)) \ 2
    For lngLoopR = LBound(ConcatCol) To UBound(ConcatCol)
        lngCounter = 0
        For lngLoopC = LBound(ParamA) To UBound(ParamA) Step 2
            If LCase(ParamA(lngLoopC)(lngLoopR)) = LCase(ParamA(lngLoopC + 1)) Then
                lngCounter = lngCounter + 1
            End If
        Next
        If lngCount = lngCounter Then
            If InStr(1, strMatch & "|", "|" & ConcatCol(lngLoopR) & "|", lngCase) = 0 Then
                lngIndex = lngIndex + 1
                varOP(lngIndex) = ConcatCol(lngLoopR)
                strMatch = strMatch & "|" & ConcatCol(lngLoopR)
            End If
        End If
    Next
    If lngIndex Then
        ReDim Preserve varOP(1 To lngIndex)
        CONCATIFS = Join(varOP, Delim)
    End If
    
End Function