Concatinating with if statements

[Anzibanonzi]

Hello!

I have gigantic spreadsheet with about 20 columns and thousands of rows. I'm only going to give a subset of the problem to make this easier.

Columns B, C & D are categories that can be assigned values. I want to add a column, E that lists the names of the column, if the row has a value in that column.

e.g.


A B C D E
1 0 0 0
2 1 0 0 B
3 5 2 1 B/C/D
4 0 2 4 C/D
5 0 0 2 D

currently I've added hidden rows F,G,H. They return the names of "B","C","D" as a string if B,C,D is a non-zero value. Then column E concats(F,"/",G,"/"H).

This is not a very elegant solution and leaves me with extraneous "/" if all three columns do not have values. Does anyone have any other ideas of how to do this? Eventually, I will be adding more conditions, and I'd like to figure out how to use if and concat together.

Thank you!

[thameem127]

upload ur file

[thameem127]

try this formula =IF(B2<>0,IF(C2<>0,IF(D2<>0,CONCATENATE(F2,G2,H2))))

[thameem127]

See the solution here concatenate.xlsx

[martindwilson]

if cells f g h have no spaces in their values/strings
=SUBSTITUTE(TRIM(F1&" "&G1&" "&H1)," ","/")
it they do then a longer
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(F10," ","^")&" "&SUBSTITUTE(G10," ","^")&" "&SUBSTITUTE(H10," ","^"))," ","/"),"^"," ")
see attached

[Anzibanonzi]

=IF(AND(NOT(B1=0),(C1=0),(D1=0)),"B",IF(AND(NOT(B1=0),NOT(C1=0),(D1=0)),"B/C",IF(AND(NOT(B1=0),NOT(C1=0),NOT(D1=0)),"B/C/D",IF(AND((B1=0),NOT(C1=0),(D1=0)),"C",IF(AND((B1=0),NOT(C1=0),NOT(D1=0)),"C/D",IF(AND((B1=0),(C1=0),NOT(D1=0)),"D",""))))))


This works too. But I'd rather not list out each condition as it is going to eventually be more complicated.

[Anzibanonzi]

That works well too! Thank you!