How can I use a VBA code on an example ?

[public]

I have the following VBA code (thanks for this code: admin - excelfox.com forum):

Option Explicit

Function CONCATIFS(ByVal ConcatCol As Variant, ByVal Delim As String, ParamArray ParamA() As Variant) As String
    
    '//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 Len(Trim(ConcatCol(lngLoopR))) 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
        End If
    Next
    If lngIndex Then
        ReDim Preserve varOP(1 To lngIndex)
        CONCATIFS = Join(varOP, Delim)
    End If
    
End Function

Which has the following function:

=CONCATIFS(
the rows can be concatenated;
concatenation separator;
the rows of criterion 1;the value you are looking for in criterion 1;
the rows of criterion 2;the value you are looking for in criterion 2)

Example: =CONCATIFS($C$2:$C$50,", ",$A$2:$A$50,F$1,$B$2:$B$50,$E2)

Now, I would like to use CONCATIFS (see the attached file below).
So, in this file are three columns: Motivation, Names and A + B. Since Column B is filled randomly, depending on the B values, column C will be automatically populated. In the example from the file, now in cell F2, I would like to list (separated by ";") only the content of C2, C3 and C4 cells. How can I do this?

[Kaper]

If I understand you well, you would like to use:

=CONCATIFS(C2:C11,"; ",B2:B11,"<>")

but the function do not work with "<>" as for instance COUNTIF etc.

Simple solution would be by modifying this :

            If LCase(ParamA(lngLoopC)(lngLoopR)) = LCase(ParamA(lngLoopC + 1)) Then
                lngCounter = lngCounter + 1
            End If

by adding "is not empty" check:

            If (ParamA(lngLoopC + 1) = "<>" And Len(ParamA(lngLoopC)(lngLoopR)) > 0) Or _
              LCase(ParamA(lngLoopC)(lngLoopR)) = LCase(ParamA(lngLoopC + 1)) Then
                lngCounter = lngCounter + 1
            End If

See attached file. (Note that it stil do not work as expected with such parameters as ">10" etc.)