Is there a "concatenateif" type function? (>30 options) in Excel
 

Each row from 2 on contains a product item in colum A. The top row, colums C
on, contain product options, 30. Below that, each row has place to select
the product option (y/n). Column B must get all the options selected as a
concatenated text string. concatenate(if(c2="y",c$1,""),",
",if(d2="y",d$1,""),", "...... doesnt work because 30 text entries are
needed, it is clumsy, takes forever to write up and is a mission to
modify/edit. Is there a better way?

Hi Denzil

Can you explain a little more about what you do with the result in column B?
Dependent upon what you are trying to achieve there may be other methods
that this concatenation which you have found does not work.

Regards

Roger Govier


Denzil B wrote:
Each row from 2 on contains a product item in colum A. The top row, colums C
on, contain product options, 30. Below that, each row has place to select
the product option (y/n). Column B must get all the options selected as a
concatenated text string. concatenate(if(c2="y",c$1,""),",
",if(d2="y",d$1,""),", "...... doesnt work because 30 text entries are
needed, it is clumsy, takes forever to write up and is a mission to
modify/edit. Is there a better way?

Hi Roger -

Basically trying to create a text string consisting of the selected options
from row 1, col C onwards, as selected in the subsequent rows of col C
onwards. Hope this "table" below shows it:

Selected Options Op 1 Op 2 Op 3 Op 4
Product 1 Op 1, Op 2, Op 3, Op 4 y y y y
Product 2 Op 1, y
Product 3 Op 2, Op 4 y y

Cheers

Denzil


"Roger Govier" wrote:

Hi Denzil

Can you explain a little more about what you do with the result in column B?
Dependent upon what you are trying to achieve there may be other methods
that this concatenation which you have found does not work.

Regards

Roger Govier


Denzil B wrote:
Each row from 2 on contains a product item in colum A. The top row, colums C
on, contain product options, 30. Below that, each row has place to select
the product option (y/n). Column B must get all the options selected as a
concatenated text string. concatenate(if(c2="y",c$1,""),",
",if(d2="y",d$1,""),", "...... doesnt work because 30 text entries are
needed, it is clumsy, takes forever to write up and is a mission to
modify/edit. Is there a better way?

On Fri, 23 Sep 2005 03:44:09 -0700, "Denzil B" <Denzil
wrote:

Each row from 2 on contains a product item in colum A. The top row, colums C
on, contain product options, 30. Below that, each row has place to select
the product option (y/n). Column B must get all the options selected as a
concatenated text string. concatenate(if(c2="y",c$1,""),",
",if(d2="y",d$1,""),", "...... doesnt work because 30 text entries are
needed, it is clumsy, takes forever to write up and is a mission to
modify/edit. Is there a better way?

Seems like a UDF would be the way to go.

The following is a suggestion.

It's relatively *hard-coded* in that the function takes no arguments, but
rather sets up the ranges within the function itself.

I have assumed that your option names are in row 1; and your product items are
in column A; and that the function will always be in column B.

In addition, I have assumed that the only acceptable entry in the selection
area is a "y".

All of this can be modified.

In any event, to enter the function, <alt<F11 opens the VB Editor. Ensure
your project is highlighted in the project explorer window, then Insert/Module
and paste the code below into the window that opens.

To use the function, enter
=ProdOptions()

into some cell in Column B. It will return the concatenation of the item in
Column A of that row with the strings from row 1 of any cell containing a "y"
in the remainder of the row.

=====================================
Function ProdOptions()
Application.Volatile

Dim FuncAddr As Range
Dim FuncRow As Long
Dim i As Long

Const OptionNamesRow As Long = 1
Const ProdNamesCol As Long = 1

Set FuncAddr = Application.Caller
FuncRow = FuncAddr.Row

If FuncAddr.Column < 2 Then
ProdOptions = CVErr(xlErrRef)
Exit Function
End If

ProdOptions = Cells(FuncRow, 1).Text

For i = 3 To 256
If Cells(FuncRow, i) = "y" Then
ProdOptions = ProdOptions & Cells(OptionNamesRow, i)
End If
Next i

End Function
===========================
--ron