Concatenate and IF Formula

[Ryske]

Hi Everyone!
Below is the Row 1 is a commodity and in row 2 is the number of pallets. Anything that more than "0" the commodity will appear in "Output" but with "/" as a separator in each commodity, which I do not know what formula would it be? Thanks in advance.

=CONCATENATE(IF(A2>0,"BREAD",""),IF(B2>0,"FROZEN",""),IF(C2>0,"WATER",""),IF(D2>0,"FFV",""))

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]BREAD[/TD]
[TD]FROZEN[/TD]
[TD]WATER[/TD]
[TD]FFV[/TD]
[TD]TOTAL[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]BREAD/FROZEN/WATER/FFV[/TD]
[/TR]
</tbody>[/TABLE]

 

[S.H.A.D.O.]

Welcome to the board Ryske,

How about something like this:

=CONCATENATE(IF(A2>0,A2&" BREAD/",""),IF(B2>0,B2&" FROZEN/",""),IF(C2>0,C2&" WATER/",""),IF(D2>0,D2&" FFV",""))

I hope this helps!

 

[Eric W]

If you have one of the newer versions of Excel (365 or some 2016), you can use the TEXTJOIN:


=TEXTJOIN("/",TRUE,IF(A2:D2>0,A$1:D$1,""))


or


=TEXTJOIN("/",TRUE,IF(A2:D2>0,A2:D2&" "&A$1:D$1,""))


both confirmed with Control+Shift+Enter.




If not, you can use S.H.A.D.O.'s formula. However, do you care if the result has a trailing "/" ? That will happen if FFV = 0. It would take a longer formula to get around that, something like:

=IF(SUM(A2:D2),SUBSTITUTE(IF(A2>0,"BREAD/","")&IF(B2>0,"FROZEN/","")&IF(C2>0,"WATER/","")&IF(D2>0,"FFV/",""),"/","",COUNTIF(A2:D2,">0")),"")

 

[Ryske]

Thank you S.H.A.D.O., this helps!

 

[Ryske]

Hello Eric W, thanks, this helps as well. I have used the first "textjoin" formula and it works perfectly.
To answer your question, FFV should not have "/". I have removed the "/" from FFV, the desired output is achieved. Thanks once again!

 

[Nishant94]

Although you have got your answer, here is my way of doing it. Just few modification to the above answers.

Excel 2016
	A	B	C	D	E	F
1	BREAD	FROZEN	WATER	FFV	TOTAL	OUTPUT
2	2	2	2	5	11	BREAD/FROZEN/WATER/FFV/
3	1	0	2	6	9	BREAD/WATER/FFV/
4	4	2		5	11	BREAD/FROZEN/FFV/
Sheet3

Cell Formulas
Range	Formula
F2	=CONCATENATE(IF(A2,$A$1&"/",""),IF(B2,$B$1&"/",""),IF(C2,$C$1&"/",""),IF(D2,$D$1&"/",""))

OR

Unknown
	A	B	C	D	E	F
1	BREAD	FROZEN	WATER	FFV	TOTAL	OUTPUT
2	2	2	2	5	11	BREAD/FROZEN/WATER/FFV
3	1	0	2	6	9	BREAD/WATER/FFV
4	4	2		5	11	BREAD/FROZEN/FFV
Sheet3

Cell Formulas
Range	Formula
F2	{=TEXTJOIN("/",,IF(A2:D2,$A$1:$D$1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ryske]

Thank you Nishant, another formula that I can use in future.

 

[S.H.A.D.O.]

Thanks for the feedback Ryske, I am glad you have got it sorted.

 

[DineshBabu Kondalsamy]

You can use both formulas,,

1. =CONCATENATE(IF(A2>0,A2&" BREAD/",""),IF(B2>0,B2&" FROZEN/",""),IF(C2>0,C2&" WATER/",""),IF(D2>0,D2&" FFV",""))

2. =IF(SUM(A2:D2),SUBSTITUTE(IF(A2>0,"$A$1&"/","")&IF(B2>0,"$B$1&"/","")&IF(C2>0,"$C$1&"/","")&IF(D2>0,"$D$1&"/",""),"/","",COUNTIF(A2:D2,">0")),"")


Thanks,
Dinesh Babu K
****** id="cke_pastebin" style="position: absolute; top: 41px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

<tbody>
[TD="align: left"]=CONCATENATE( IF(A2,$A$1&"/",""),IF(B2,$B$1&"/",""),IF(C2,$C$1&"/",""),IF(D2,$D$1&"/","") )[/TD]

</tbody>

<tbody>
[TD="align: left"]=CONCATENATE( IF(A2,$A$1&"/",""),IF(B2,$B$1&"/",""),IF(C2,$C$1&"/",""),IF(D2,$D$1&"/","") )[/TD]

</tbody>

<tbody>
[TD="align: left"]=CONCATENATE( IF(A2,$A$1&"/",""),IF(B2,$B$1&"/",""),IF(C2,$C$1&"/",""),IF(D2,$D$1&"/","") )[/TD]

</tbody>

<tbody>
[TD="align: left"]=CONCATENATE( IF(A2,$A$1&"/",""),IF(B2,$B$1&"/",""),IF(C2,$C$1&"/",""),IF(D2,$D$1&"/","") )[/TD]

</tbody>

</body>

 

[DineshBabu Kondalsamy]

Hi,

You can ues both the formulas,

1. =CONCATENATE(IF(A2>0,A2&" BREAD/",""),IF(B2>0,B2&" FROZEN/",""),IF(C2>0,C2&" WATER/",""),IF(D2>0,D2&" FFV",""))

2. =IF(SUM(A2:D2),SUBSTITUTE(IF(A2>0,$A$1&"/","")&IF(B2>0,$B$1&"/","")&IF(C2>0,$C$1&"/","")&IF(D2>0,$D$1&"/",""),"/","",COUNTIF(A2:D2,">0")),"")


Thanks,
Dinesh Babu.