Include "switched on" cells in text string

[jberg123]

Column A contains 1's or 0's, 1 meaning what's in the cell to the right of it (in B) should be included, 0 excluded.

I then want in C1, say, a text such as ="The following companies are not included in the mean: "[list of excluded]

Now, I obviously want the list of excluded companies (the names in column B) to be separated with comma and the last on introduced with an " and " in front of it.

I also want this all to be flexible, so I can turn off and on companies using the switch in column A.

Is there any clever way to do this?

Was thinking maybe there's a funky LISTIF-CSE-function type one could construct... but can't get anywhere.

Many thanks...

 

[shg]

UDF?

Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",") As String
    Dim iRow        As Long
    Dim iCol        As Long
 
    For iRow = 1 To rInp.Rows.Count
        For iCol = 1 To rInp.Columns.Count
            If avbIf(iRow, iCol) Then CatIf = CatIf & rInp(iRow, iCol) & sSep
        Next iCol
    Next iRow
 
    If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function

E.g.,

       A --B--- --------------C---------------
   1   0 Alan   Excluded: Alan,Dan,Frank,Henry
   2   1 Bob                                  
   3   1 Chuck                                
   4   0 Dan                                  
   5   1 Ed                                   
   6   0 Frank                                
   7   1 George                               
   8   0 Henry                                
   9   1 Ian                                  
  10   1 James

The formula in C1 is

="Excluded: " & catif(A1:A10=0, B1:B10)

... which MUST be confirmed with Ctrl+Shift+Enter

 

[jberg123]

UDF?

Function CatIf(avbIf As Variant, _
               rInp As Range, _
               Optional sSep As String = ",") As String
    Dim iRow        As Long
    Dim iCol        As Long
 
    For iRow = 1 To rInp.Rows.Count
        For iCol = 1 To rInp.Columns.Count
            If avbIf(iRow, iCol) Then CatIf = CatIf & rInp(iRow, iCol) & sSep
        Next iCol
    Next iRow
 
    If Len(CatIf) Then CatIf = Left(CatIf, Len(CatIf) - Len(sSep))
End Function

E.g.,

       A --B--- --------------C---------------
   1   0 Alan   Excluded: Alan,Dan,Frank,Henry
   2   1 Bob                                  
   3   1 Chuck                                
   4   0 Dan                                  
   5   1 Ed                                   
   6   0 Frank                                
   7   1 George                               
   8   0 Henry                                
   9   1 Ian                                  
  10   1 James

The formula in C1 is

="Excluded: " & catif(A1:A10=0, B1:B10)

... which MUST be confirmed with Ctrl+Shift+Enter

Brilliant! Many thanks!