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...
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,201
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top