I have some raw data on Excel that is organized in a way that is not easily readable. For example, for a table showing which user likes which color:
A B C D E User | Red | Green | Orange | Blue | Yellow A 0 1 0 1 0 B 0 0 1 1 0 C 1 0 0 0 1
So, user A likes Green and Blue, B likes Orange and Blue, and C likes Red and Yellow.
This is useful for us because I can use this to calculate frequency, but I also need the data in human readable form.
I would like to add a single column that says the colors that a user picked, like: User A - Green + Blue.
But I can't seem to do this with
CONCATENATE(IF(A2=1, $A$1), +, IF(B2=1, $B$1)) and so on because the
IF function doesn't work inside
CONCATENATE. I also cannot add too many columns because my data has multiple columns already.
Is there a function I can use to turn this binary data into a clean string that can fit in on column?