lundi 23 février 2015

Trying to concatenate top row cells if column below has 1 in excel 2013

I'm looking at a large database of 1s and 0s with named columns, like this:


l red l blue l green l orange l purple l


l 0 l 0 l 1 l 0 l 1 l


l 0 l 1 l 0 l 0 l 0 l


(Sorry for the awful table)


I want to concatenate all the headings (by row) where the row has a "1" below that heading. So ideally the first one would equal "green, purple" and the second would just read "blue". I have a large amount of data so anything with nesting of a hundred "IF" functions doesn't make sense.


I've tried



=IF(B1:B5=1, CONCATENATE(A1:A5), "")



and a couple things close to that, but I'm not finding an obvious way to get it. I also don't really have time or enough knowledge to deal with VBA. I appreciate all help, thanks!


Aucun commentaire:

Enregistrer un commentaire