How to return multiple text values based on a row tag

[MaulTiper]

Hi,

I have a spreadsheet where users place an "x" in various columns to mark the options they want. I am having trouble returning a list of all the options they selected on my summary sheet of all the options they selected.

I have tried array formulaes but it seems to only work to add up numbers. I would love to get back the text names of each option in a single cell separated by a comma. Or even in different cells if that is easier.

Here is the array formulae I am working with that returns the numbers:
=IF(O17="x",SUM(IF(F59:F63="x",E59:E63,"")),"")

Anyone have any suggestions.

Thanks
Matt

 

[Aladin Akyurek]

http://www.mrexcel.com/board2/viewtopic.php?t=129601

 

[MaulTiper]

Thanks but not exactly what I mean.

Sample, where - is the column separator:

Options - Flag
Red - x
Blue -
Green -
Big - x
Small -
Hot - x
Cold -

Summary cell
Cell one would ideally read (Red, Big, Hot)

Any way to accomplish this?

 

[daniels012]

This should work:

=IF(M13="x",L13,"")&IF(M14="x"," "&L14,"")&IF(M15="x"," "&L15,"")&IF(M16="x"," "&L16,"")&IF(M17="x"," "&L17,"")&IF(M18="x"," "&L18,"")

If your data is in M13 thru L18

Michael

 

[MaulTiper]

My data is much longer than nested if's will allow, which is why I have been struggling with these array formula's.

This array formulae will work to add numbers:

=SUM(IF($F$59:$F$63="x",$E$59:$E$63,""))

I am pretty convinced that something like the below will work for the text but I am obviously missing something.

=SUM(IF($F$59:$F$63="x",CONCATENATE($C$59:$C$63),""))

 

[Aladin Akyurek]

Thanks but not exactly what I mean.

Sample, where - is the column separator:

Options - Flag
Red - x
Blue -
Green -
Big - x
Small -
Hot - x
Cold -

Summary cell
Cell one would ideally read (Red, Big, Hot)

Any way to accomplish this?

The formula system I quoted would give you

Red
Big
Hot

in the result range in separate cells.

Collecting them in a single cell is also possible, but that is limited to a result string of 255 chars.

 

[MaulTiper]

While you are correct that the solution you referenced will provide a list of option values. Again, it is not exactly what I hoped for.

You see I have dozens of colmns of x's representing a huge matrix of diffent customers and "options" so implementing that type of solution is not ideal.

If you or anyone else knows of a more stremlined solution I would appreciate any guideance.

I do appreciate your input to date!

Matt

 

[MaulTiper]

Thanks for all the help... through all your posts and some heavy research on my part I cam up with the solution I was looking for. For those of you who are in the same boat here is my solution:

I borrowed the following code from http://www.mcgimpsey.com/ and entered it in a VB module.

'*****************************************
'Purpose: Concatenate all cells in a range
'Inputs: rRng - range to be concatenated
' sDelimiter - optional delimiter
' to insert between cell Texts
'Returns: concatenated string
'*****************************************
Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
MultiCat = MultiCat & sDelim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


Once in there, it allows me to use my array formula like the one below perfectly. Where B6:B8 is my range containing my tags, and A6:A8 is the range containing my text descriptions.

=(IF(B6:B8="x",MultiCat(A6:A8," ,"),""))

God love Mother Internet!!!!!

Thanks again,
Matt

 

[Aladin Akyurek]

Thanks for all the help... through all your posts and some heavy research on my part I cam up with the solution I was looking for. For those of you who are in the same boat here is my solution:

I borrowed the following code from http://www.mcgimpsey.com/

...

Do also a search on MCONCAT and ACONCAT on this site. Bear also in mind that you'll need complex formulas if you inted to further process the results of the formulas with such functions.