Creating text string based on column titles and values below

[h2whoa]

Hi all,

So, in Excel 2010 I'm wondering if there's a way to create a text string at the end of a row based on whether or not columns have a value in that row. I've put in an example below, where column F outputs the string based on the values in columns B–E.

	A	B	C	D	E	F
1	id	Test A	Test B	Test C	Test D	Test string
2	1A		Yes			Test B
3	2B	Yes		Yes	Yes	Test A, Test C, Test D
4	3C	Yes			Yes	Test A, Test D

Thanks!

[hemesh]

Hello and welcome to the forum
there are a lot of way but first let us know how many columns of data you have

[h2whoa]

Hi Hemesh,

Thanks! I have 9 columns of data to run this through (and 1500 rows)

[hemesh]

then you can use UDF provided by Tigeravater
http://www.excelforum.com/tips-and-t...geravatar.html
copy paste the code by hitting alt+F11
then go to insert tab click module copy paste code and then use the same as worksheet function as in J2
copy paste below then hold control shift together and then hit enter to make it array formula

=ConcatAll(IF(B2:K2="yes",$B$1:$K$1,""),",")

[h2whoa]

then you can use UDF provided by Tigeravater
http://www.excelforum.com/tips-and-t...geravatar.html
copy paste the code by hitting alt+F11
then go to insert tab click module copy paste code and then use the same as worksheet function as in J2
copy paste below then hold control shift together and then hit enter to make it array formula

=ConcatAll(IF(B2:K2="yes",$B$1:$K$1,""),",")

Awesome! Thank you so much!

[hemesh]

you are welcome !

if this solves your query then you can mark thread as solved. Please read the signatures in post#2.

I would appreciate if you consider adding rep to the TIGERAVATER who wrote this code to make life much easy