Virtual column expression to create a 'switch' list of values from multiple fields

[tjr2484]

Hi community,

I've been struggling with this for some time so hoping someone might know the answer.

Consider the following table and data:

Apple	Banana	Orange	Pear	Lemon
TRUE	FALSE	TRUE	TRUE	FALSE
FALSE	TRUE	TRUE	TRUE	FALSE
FALSE	TRUE	TRUE	TRUE	TRUE

I want to create a 6th column, a virtual column (type is list), to create a list from each of these five fields, where only the value is included if it is TRUE, and 'switch' it for a string (the column header). Here is the desired output, structured as a list.

Apple	Banana	Orange	Pear	Lemon	Virtual Column
TRUE	FALSE	TRUE	TRUE	FALSE	Apple, Orange, Pear
FALSE	TRUE	TRUE	TRUE	FALSE	Banana, Orange, Pear
FALSE	TRUE	TRUE	TRUE	TRUE	Banana, Orange, Pear, Lemon

This is the closest I can get:

CONCATENATE(
  IFS([Apple] = "TRUE", "Apple", ""),
  IFS([Banana] = "TRUE", "Banana", ""),
  IFS([Orange] = "TRUE", "Orange", ""),
  IFS([Pear] = "TRUE", "Pear", ""),
  IFS([Lemon] = "TRUE", "Lemon", "")
)

However this results in a trailing comma.

Any help appreciated.

[Suvrutt_Gurjar · Accepted solution]

Please try 

LIST(IF([Apple], "Apple",""),
IF([Banana], "Banana",""),
IF([Orange], "Orange",""),
IF([Pear],"Pear",""),
IF([Lemon],"Lemon","")
)-LIST("")

for the first example with fruit names. Please replicate suitably for the second example of days.

[Apple], [Banana]......[Lemon] are assumed to be Y/N type columns.

[AleksiAlkio]

It would be better to use just a normal column as all values are coming from the same and one row. Using IFS() would also be more simple 😉

[Suvrutt_Gurjar]

Yes , correct @AleksiAlkio