Grouping based on a condition

jschrock

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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What version of Xl are you using?
 
Upvote 0
In that case you don't have the Textjoin function, so this would be easier with VBA. Is that ok?
 
Upvote 0
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()??
 
Upvote 0
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
 
Upvote 0
is that what you want?

Mood:Yes/NoYes/NoMood
AgitatedYYAgitated, Angry, Depressed
AngryY
Anxious
Apathetic
DepressedY
Dysthymic
Elevated
Emotional
Fearful
Manic
Withdrawn
Other:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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&" ",""))," ",", ")
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top