Include "switched on" cells in text string

jberg123

New Member
Joined
Feb 24, 2011
Messages
45
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...
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,768
Office Version
  1. 2010
Platform
  1. Windows
UDF?
Code:
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.,

Code:
       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
 
Last edited:

jberg123

New Member
Joined
Feb 24, 2011
Messages
45
UDF?
Code:
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.,

Code:
       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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top