Grouping based on a condition

[jschrock]

So, I have a list of Yes/No questions and what I am trying to do is create a string of the terms that have a Y beside them. For example in the data below, I would like the cell to return Agitated, Angry, Depressed and I will reference that in a different part of a sheet. I would like for this to be dynamic, so if I put a 'Y' beside Apathetic, the string would read Agitated, Angry, Apathetic, and Depressed. I hope that makes sense. Here is the data:

[TABLE="width: 185"]
<tbody>[TR]
[TD]Mood:[/TD]
[TD]Yes/No[/TD]
[/TR]
[TR]
[TD]Agitated[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Angry[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Anxious[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apathetic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Depressed[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Dysthymic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Elevated[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emotional[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fearful[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Manic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Withdrawn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other:[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 185"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I know I could do this with a bunch of If statements stacked together, but I would love to exclude or skip the items that are blank.

Thanks in advance!!!

 

[Fluff]

Using VBA

Function jschrock(Rng As Range, Optional Crit As String = "Y")
    Dim Cl As Range
    Application.Volatile
    For Each Cl In Rng
        If Cl.Offset(, 1).Value = Crit Then jschrock = jschrock & Cl.Value & ", "
    Next Cl
    jschrock = Left(jschrock, Len(jschrock) - 2)
End Function

Used in the sheet like

Book1
	AO	AP	AQ
25	Agitated	Y	Agitated, Anxious, Apathetic, Dysthymic, Manic
26	Angry
27	Anxious	Y
28	Apathetic	Y
29	Depressed
30	Dysthymic	Y
31	Elevated
32	Emotional
33	Fearful
34	Manic	Y
35	Withdrawn
36	Other:
Master

Cell Formulas
Range	Formula
AQ25	=jschrock(AO25:AO36,"Y")

 

[sandy666]

another way with Pivot Table

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Yes/No[/td][td=bgcolor:#DDEBF7]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mood:[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agitated[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Angry[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Depressed[/td][td][/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Yes/No[/td][td=bgcolor:#DDEBF7]Y[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mood:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Agitated[/td][td=bgcolor:#DDEBF7]Angry[/td][td=bgcolor:#DDEBF7]Depressed[/td][/tr]
[/table]

 

[jschrock]

Thank you guys so much for all of the help!!! It's always awesome to see how quickly everyone responds on here and is so helpful and creative.....best free subscription, ever!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[sandy666]

You're welcome

Have a nice day