# How to return multiple text values based on a row tag

#### MaulTiper

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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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?

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

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),""))

MaulTiper said:
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.

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

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

MaulTiper said:
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.

Replies
4
Views
115
Replies
9
Views
160
Replies
2
Views
155
Replies
4
Views
189
Replies
6
Views
183

1,203,112
Messages
6,053,574
Members
444,674
Latest member
DWriter9

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

### Which adblocker are you using?

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