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]

What version of Xl are you using?

 

[jschrock]

Excel 2016

 

[Fluff]

In that case you don't have the Textjoin function, so this would be easier with VBA. Is that ok?

 

[jschrock]

If it was just for me, yes, but I am trying to build for someone else and they will need to maintain. I think it is find to do the if statement. I think it is working after I am playing with it. However, I have a different issue just to make this thing look clean. Here is the formula I am looking at:

=IF(AP25="Y",AO25&", ","")&IF(AP26="Y",AO26&", ","")&IF(AP27="Y",AO27&", ","")&IF(AP28="Y",AO28&", ","")&IF(AP29="Y",AO29&", ","")

The problem now is that the last word it displays has a , at the end. Is there a way to exclude the , if it is the last word in the string? Like maybe wrap this whole thing in something like a =Text()??

 

[arsabra]

if you put the List from D4 to D15 and you want the conditional to be at Column J Starting from J4
place this formula : from J4 to j15
IFERROR(INDEX(D4:$D$15,MATCH(E4,E4:$E$15,0),1),"")
only the case with Y will appear under J

Sabra

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Mood:[/td][td=bgcolor:#5B9BD5]Yes/No[/td][td][/td][td=bgcolor:#70AD47]Yes/No[/td][td=bgcolor:#70AD47]Mood[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Agitated[/td][td=bgcolor:#DDEBF7]Y[/td][td][/td][td=bgcolor:#E2EFDA]Y[/td][td=bgcolor:#E2EFDA]Agitated, Angry, Depressed[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Depressed[/td][td=bgcolor:#DDEBF7]Y[/td][td][/td][td][/td][td][/td][/tr]

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

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

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

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

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

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

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

 

[jschrock]

Yes. That's what I was going for. The post before is awesome, but when trying to stack them together within say a form, like you have in one box.

 

[sandy666]

you've Ex2016 so you've also PowerQuery aka Get&Transform

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Yes/No"}, {{"Count", each _, type table}}),
    Filter = Table.SelectRows(Group, each ([#"Yes/No"] = "Y")),
    List = Table.AddColumn(Filter, "Mood", each List.Distinct(Table.Column([Count],"Mood:"))),
    Extract = Table.TransformColumns(List, {"Mood", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract[/SIZE]

post#7 contain the result

 

[Fluff]

If none of the words in col AO contain spaces, you could use
=SUBSTITUTE(TRIM(IF(AP25="Y",AO25&" ","")&IF(AP26="Y",AO26&" ","")&IF(AP27="Y",AO27&" ","")&IF(AP28="Y",AO28&" ","")&IF(AP29="Y",AO29&" ",""))," ",", ")