Can I concatenate multiple results into 1 field?

[bruty]

I have a table that looks similar to this:

A	B	C	D	E	F	G	H	I
Q_#	Q_Text	R_#	R_Text	+ve Score	-ve Score	+ve Text	-ve Text
Q1	I look forward to coming to work.	1​	Never	0​	1​		Never
Q1	I look forward to coming to work.	2​	Rarely	0​	1​		Rarely
Q1	I look forward to coming to work.	3​	Sometimes	0​	0​
Q1	I look forward to coming to work.	4​	Often	1​	0​	Often
Q1	I look forward to coming to work.	5​	Always	1​	0​	Always

In Column I I would like a concatenation of the text from Columns G & H, so ideally it will look like this in the end:

A	B	C	D	E	F	G	H	I
Q_#	Q_Text	R_#	R_Text	+ve Score	-ve Score	+ve Text	-ve Text
Q1	I look forward to coming to work.	1​	Never	0​	1​		Never	Never/Rarely
Q1	I look forward to coming to work.	2​	Rarely	0​	1​		Rarely	Never/Rarely
Q1	I look forward to coming to work.	3​	Sometimes	0​	0​
Q1	I look forward to coming to work.	4​	Often	1​	0​	Often		Often/Always
Q1	I look forward to coming to work.	5​	Always	1​	0​	Always		Often/Always

What formula can I put into Column I to produce this, or am I trying to do something that is impossible?

Just to clarify, the questions will have a different number of response options and in different orders, so I can't just use a formula to concatenate specific cells, as these will change.

If I need to add extra columns that is not a problem (so if I need a separate column to concatenate +ve text and one for -ve text I will be able to work with this).

 

[awoohaw]

What version of excel are you using? Please update your profile so the version appears on your button, and the forum knows what kind of solution would best work for you.

How can we determine what are valid values to concatenate? How did you pick rows 1 and 2 for column I in the first two rows, and rows 4 and 5 for the last two?

 

[bruty]

Apologies. I'm on 365 and obviously didn't make myself clear originally.

The values in Column I need to be a concatenation of the text in Columns G & H for each question. Column G & H are the scored response options for each question. Columns E & F denote whether or not it is a positive or negative scored question, and all negative text needs to be concatenated together and all positive needs to be together.

Hope this makes a bit more sense. I'm deep in the weeds here, so know I'm probably not making the most sense.

 

[awoohaw]

maybe this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Q_#	Q_Text	R_#	R_Text	+ve Score	-ve Score	+ve Text	-ve Text
2	Q1	I look forward to coming to work.	1	Never	0	1		Never	Never/Rarely	Never/Rarely
3	Q1	I look forward to coming to work.	2	Rarely	0	1		Rarely	Never/Rarely	Never/Rarely
4	Q1	I look forward to coming to work.	3	Sometimes	0	0
5	Q1	I look forward to coming to work.	4	Often	1	0	Often		Often/Always	Often/Always
6	Q1	I look forward to coming to work.	5	Always	1	0	Always		Often/Always	Often/Always
Sheet2

Cell Formulas
Range	Formula
J2:J6	J2	=SWITCH(SIGN(C2-3), 1,TEXTJOIN("/",,TRANSPOSE(FILTER($D$2:$D$6,$E$2:$E$6=1,""))), 0,"", -1,TEXTJOIN("/",,TRANSPOSE(FILTER($D$2:$D$6,$F$2:$F$6=1,""))))

D$2:$D$6,$F$2:$F$6=1,""))))[/XD][/XR][/RANGE]

 

[Cubist]

Try:

=IF(COUNTA(G2:H2)=0,"",IF(F2,TEXTJOIN("/",,$H$2:$H$6),TEXTJOIN("/",,$G$2:$G$6)))

 

[bruty]

Thanks both. Will give them a go and see what works