Merge multiple cells based on contents of other cells

[slam]

Hi all!

Below is a silly mockup of what I'm trying to achieve. I am trying to automate the Order column. First it should add the person's name to the Order column, then where there's an x in the animal columns, it should add the respective text from row 1 to the order column.

Example.xls
	A	B	C	D	E	F
1	Name	Dog	Cat	Cow	Horse	Order
2	James	x		x		James. Dog. Cow.
3	Andrew		x		x	Andrew. Cat. Horse.
4	Stephanie	x	x	x	x	Stephanie. Dog. Cat. Cow. Horse
Sheet3

I'm familiar with the likes of this formula below, but then I need to automate this based on the x's, as there are many rows.

=A2&". "&B1&". "&D1&"."

Any help would be greatly appreciated!

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	Name	Dog	Cat	Cow	Horse	Order
2	James	x		x		James. Dog. Cow
3	Andrew		x		x	Andrew. Cat. Horse
4	Stephanie	x	x	x	x	Stephanie. Dog. Cat. Cow. Horse
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=TEXTJOIN(". ",1,A2,IF(B2:E2="x",B$1:E$1,""))

 

[slam]

Hi Eric - Thank you! Realized I had to enter it as an array formula, but maybe that's because this is on Excel 2019. Works perfectly anyway, and I modified for my actual column references.

 

[Eric W]

Good job realizing that 2019 needs the array formula, newer versions default to array formula by default.

Glad I could help!