List cells with "YES" Value

[surkdidat]

Hi all

In Row 1 I have the heading names i.e AAA BBB CCC DDD etc
What I would like in column P, it looks at that the cell value, and if there is a yes. it lists it in column P
So :-

 -----L---- | -----M---- | -----N---- | -----O---- | -----P---- | 
----AAA--- | ---BBB--- | ---CCC--- | ---DDD--- | ANSWER-| 

--- YES----|-----YES---|-----NO----|------NO---| AAA BBB  |
-----NO----|-----NO----|-----NO----|-----NO----| (no value) |
-----NO----|-----YES---|-----NO----|-----NO----| ----BBB--- |
-----NO----|-----YES---|-----NO----|----YES----| BBB DDD   |

 

[JackDanIce]

Not sure this is do-able with standard Excel, do you mean with a macro?

If so, what is the name of the sheet the data is on and what column defines the last row of data?

 

[wideboydixon]

Excel 2016 solution:

Book1
	L	M	N	O	P
1	AAA	BBB	CCC	DDD	ANSWER
2	YES	YES	NO	NO	AAA BBB
3	NO	NO	NO	NO
4	NO	YES	NO	NO	BBB
5	NO	YES	NO	YES	BBB DDD
Sheet1

Cell Formulas
Range	Formula
P2	{=TRIM(CONCAT(IF($L2:$O2="YES",$L$1:$O$1&" ","")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Previous versions:

Book1
	L	M	N	O	P
1	AAA	BBB	CCC	DDD	ANSWER
2	YES	YES	NO	NO	AAA BBB
3	NO	NO	NO	NO
4	NO	YES	NO	NO	BBB
5	NO	YES	NO	YES	BBB DDD
Sheet1

Cell Formulas
Range	Formula
P2	=TRIM(IF($L2="YES",$L$1&" ","")&IF($M2="YES",$M$1&" ","")&IF($N2="YES",$N$1&" ","")&IF($O2="YES",$O$1&" ",""))

WBD

 

[surkdidat]

Excellent. That's going into my collection, very useful!!

Excel 2016 solution:

	L	M	N	O	P
AAA	BBB	CCC	DDD	ANSWER
YES	YES	NO	NO	AAA BBB
NO	NO	NO	NO
NO	YES	NO	NO	BBB
NO	YES	NO	YES	BBB DDD

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]{=TRIM(CONCAT(IF($L2:$O2="YES",$L$1:$O$1&" ","")))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Previous versions:

	L	M	N	O	P
AAA	BBB	CCC	DDD	ANSWER
YES	YES	NO	NO	AAA BBB
NO	NO	NO	NO
NO	YES	NO	NO	BBB
NO	YES	NO	YES	BBB DDD

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]=TRIM(IF($L2="YES",$L$1&" ","")&IF($M2="YES",$M$1&" ","")&IF($N2="YES",$N$1&" ","")&IF($O2="YES",$O$1&" ",""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD