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

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula P2 {=TRIM(CONCAT(IF($L2:$O2="YES",$L$1:$O$1&" ","")))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Previous versions:

	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

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula P2 =TRIM(IF($L2="YES",$L$1&" ","")&IF($M2="YES",$M$1&" ","")&IF($N2="YES",$N$1&" ","")&IF($O2="YES",$O$1&" ","")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

WBD