A
B
C
D
1
1
 dog dog, chicken C1 and down: {=CatIf($A$1:$A$9 = A1, $B$1:$B$9, ", ")}
2
2
 cat cat, pig, rat
3
1
 chicken dog, chicken
4
7
 sheep sheep
5
4
 dog dog
6
5
 cow cow, cow
7
2
 pig cat, pig, rat
8
5
 cow cow, cow
9
2
 rat cat, pig, rat 


Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",", _
               Optional bCatEmpty As Boolean = False) As String
  ' shg 2007
  ' UDF only

  ' Catenates the elements of rInp separated by sSep where the corresponding
  ' element of avbIf is True. Empty cells ignored unless bCatEmpty is True.

  Dim iRow          As Long
  Dim iCol          As Long
  Dim i             As Long

  On Error Resume Next
  i = UBound(avbIf, 2)

  If Err.Number Then
    ' avbIf is 1D
    For iRow = 1 To rInp.Rows.Count
      For iCol = 1 To rInp.Columns.Count
        i = i + 1
        If avbIf(i) Then
          If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
            CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
          End If
        End If
      Next iCol
    Next iRow
  Else
    ' it's 2D
    For iRow = 1 To rInp.Rows.Count
      For iCol = 1 To rInp.Columns.Count
        If avbIf(iRow, iCol) Then
          If bCatEmpty Or Not IsEmpty(rInp(iRow, iCol).Value2) Then
            CatIf = CatIf & rInp(iRow, iCol).Value2 & sSep
          End If
        End If
      Next iCol
    Next iRow
  End If

  If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function