Grouping based on a condition

jschrock

New Member
Joined
Oct 5, 2011
Messages
44
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:

Mood:Yes/No
AgitatedY
AngryY
Anxious
Apathetic
DepressedY
Dysthymic
Elevated
Emotional
Fearful
Manic
Withdrawn
Other:

<tbody>
</tbody>

<tbody>
</tbody>
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
What version of Xl are you using?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
In that case you don't have the Textjoin function, so this would be easier with VBA. Is that ok?
 

jschrock

New Member
Joined
Oct 5, 2011
Messages
44
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

New Member
Joined
May 12, 2014
Messages
6
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,278
is that what you want?

Mood:Yes/NoYes/NoMood
AgitatedYYAgitated, Angry, Depressed
AngryY
Anxious
Apathetic
DepressedY
Dysthymic
Elevated
Emotional
Fearful
Manic
Withdrawn
Other:
 

jschrock

New Member
Joined
Oct 5, 2011
Messages
44
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,278
you've Ex2016 so you've also PowerQuery aka Get&Transform

Code:
[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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
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&" ",""))," ",", ")
 

Forum statistics

Threads
1,078,148
Messages
5,338,522
Members
399,240
Latest member
mominul2241

Some videos you may like

This Week's Hot Topics

Top