If & Concatenate Multiple Fields

[catyanne]

I am trying to concatenate multiple fields if there is data there. G2 is what I want the data to look like, only picking up the cells with data and concatenate with the heading with + between each fields..

I managed to only get one cell and header =IF(A2="No","",CONCATENATE(TEXT(B2,$###K")," ",B1))) but can't figure out how to concatenate the other cells that have data.

	A	B	C	D	E	F	G	H
1	INVESTIGATE	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes​	-1​		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes​	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
4

 

[jasonb75]

Given that you don't have your excel version shown on your profile (please update your Account details ) this may or may not work for you.

Book1
	A	B	C	D	E	F	G
1	Investigate	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes	-1		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
Sheet4

Cell Formulas
Range	Formula
G2:G3	G2	=IF(A2="No","",TEXTJOIN(" + ",1,IF(B2:F2="","",TEXT(B2:F2,"$###K")&" "&$B$1:$F$1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[catyanne]

Given that you don't have your excel version shown on your profile (please update your Account details ) this may or may not work for you.

Book1
	A	B	C	D	E	F	G
1	Investigate	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes	-1		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
Sheet4

Cell Formulas
Range	Formula
G2:G3	G2	=IF(A2="No","",TEXTJOIN(" + ",1,IF(B2:F2="","",TEXT(B2:F2,"$###K")&" "&$B$1:$F$1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

It's Excel 2016 so Textjoin does not work. Thanks

 

[jtakw]

Hi,

It's not pretty, and took me awhile to put together, my fingers are tired from typing...
But it seems to work:

Book3.xlsx
	A	B	C	D	E	F	G
1	INVESTIGATE	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes	-1		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
Sheet859

Cell Formulas
Range	Formula
G2:G3	G2	=TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" $"&B2&" b "&" $"&C2&" c "&" $"&D2&" d "&" $"&E2&" e "&" $"&F2&" f ","$ b",""),"$ c",""),"$ d",""),"$ e",""),"$ f","")&"|"," b ","K "&B$1&" + ")," c ","K "&C$1&" + ")," d ","K "&D$1&" + ")," e ","K "&E$1&" + ")," f ","K "&F$1),"$-","-$")),"+ |",""),"|",""))

 

[jtakw]

OOps, got so caught up with joining the text string, forgot to test A column for Yes, corrected below, same formula, just added test for Yes/No:

Book3.xlsx
	A	B	C	D	E	F	G
1	INVESTIGATE	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes	-1		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
4	No	1		2	-3
5	Yes		4	-5			$4K Type 2 Var + -$5K Type 3 Var
Sheet859

Cell Formulas
Range	Formula
G2:G5	G2	=IF(A2="Yes",TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" $"&B2&" b "&" $"&C2&" c "&" $"&D2&" d "&" $"&E2&" e "&" $"&F2&" f ","$ b",""),"$ c",""),"$ d",""),"$ e",""),"$ f","")&"|"," b ","K "&B$1&" + ")," c ","K "&C$1&" + ")," d ","K "&D$1&" + ")," e ","K "&E$1&" + ")," f ","K "&F$1),"$-","-$")),"+ |",""),"|","")),"")

 

[catyanne]

OOps, got so caught up with joining the text string, forgot to test A column for Yes, corrected below, same formula, just added test for Yes/No:

Book3.xlsx
	A	B	C	D	E	F	G
1	INVESTIGATE	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes	-1		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
4	No	1		2	-3
5	Yes		4	-5			$4K Type 2 Var + -$5K Type 3 Var
Sheet859

Cell Formulas
Range	Formula
G2:G5	G2	=IF(A2="Yes",TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" $"&B2&" b "&" $"&C2&" c "&" $"&D2&" d "&" $"&E2&" e "&" $"&F2&" f ","$ b",""),"$ c",""),"$ d",""),"$ e",""),"$ f","")&"|"," b ","K "&B$1&" + ")," c ","K "&C$1&" + ")," d ","K "&D$1&" + ")," e ","K "&E$1&" + ")," f ","K "&F$1),"$-","-$")),"+ |",""),"|","")),"")

This works perfectly. Thank you. If I was to add an extra column of data. I have 8 columns of data eg B-I. Am I able to add the other columns after the highlighted?

 

[jtakw]

Here's a relatively shorter version of my formula, when I was testing the TEXT function for my original formula, for some reason it didn't work, it turned out there were hidden characters when I copied your OP data, anyway, I think this is much "cleaner"

As for your question regarding 3 more columns, Yes, you can just add on to the formula, you'll also need 3 additional SUBSTITUTE functions, let me know if you need help with that:

Book3.xlsx
	A	B	C	D	E	F	G
1	INVESTIGATE	Type 1 Var	Type 2 Var	Type 3 Var	Type 4 Var	Type 5 Var	Comment
2	Yes	-1		2		-15	-$1K Type 1 Var + $2K Type 3 Var + -$15K Type 5 Var
3	Yes	2	-1		4		$2K Type 1 Var + -$1K Type 2 Var + $4K Type 4 Var
4	No	1		2	-3
5	Yes		4	-5			$4K Type 2 Var + -$5K Type 3 Var
Sheet864

Cell Formulas
Range	Formula
G2:G5	G2	=IF(A2="Yes",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(B2,"$0K ")&B$1&TEXT(C2,"$0K ")&C$1&TEXT(D2,"$0K ")&D$1&TEXT(E2,"$0K ")&E$1&TEXT(F2,"$0K ")&F$1,"$0K "&B$1,""),"$0K "&C$1,""),"$0K "&D$1,""),"$0K "&E$1,""),"$0K "&F$1,"")&"|","Var","Var + "),"+ |",""),"")