ConcatenateIFS

[striderider]

Hi everyone,

I am trying to build a formula that uses multiple IF statements to concatenate. This is the formula I'm using for ConcatenateIF but I want to be able to add multiple IF conditions to it.

Any suggestions? Thanks in advance

Function Concatenateif(CriteriaRange As Range, condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant'Update 20150414
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    Concatenateif = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
Concatenateif = xResult
Exit Function
End Function

 

[mikerickson]

I would arrange the arguments in this order
StringsRange
Separator
CriteriaRange1
Criteria1
CriteriaRange2
Criteria2
...

That way you could use a ParamArray for the indeterminate number of CriteriaRanges.

The code would look like

Function ConcatIfs(StringsRange, Separator, ParamArray Crits() as Variant) As String

Dim i As Long, j as Long, flag as boolean

For i = 1 to StringsRange.Cells.Count
    flag = True

    For j = 0 to Ubound(Crits)/2
        If WorksheetFunction.CountIf(Crit(2*j).Item(i),  Crit(2*j+1))=0 then
            Flag = False
            Exit For
        End If
    Next j

    If Flag then
        ConcatIfs = ConcatIfs & Separator & StringsRange.Item(i)
    End If
Next i

ConcatIfs = Mid(Concatifs,Len(separator)+1)

End Function

The use of WorksheetFunction.CountIf in the test allows the user to enter inequality criteria.
Note that in the ParamArray Crits, that the even indices are ranges and the odd indices are strings.

 

[striderider]

Thanks so much for the quick response!

I copied in the code into a new module.

When I try to run the function in excel, I get the following error:

Compile Error: Sub or Function not defined
and the VBA editor opens up with the top line of the code highlighted ("Function ConcatIfs...")

Maybe I am using it wrong, I was following the convention from the ConcatenateIF formulate where it was:

=ConcatIfs(Range1, Criteria1, Range2, Criteria2, Column to Concatenate, 0)

I also tried reversing the order: Criteria1, Range1

Thanks in advance!

 

[mikerickson]

The code I posted is untested. I was trying to get across a quick idea of how you could modify your existing code to handle multiple criteria. I'm sure there are other syntax errors.