Include "switched on" cells in text string

jberg123

New Member
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
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
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!

Replies
4
Views
265
Replies
1
Views
134
Replies
10
Views
770
Replies
1
Views
89
Replies
9
Views
1K

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.

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.

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

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