jeudi 30 juillet 2015

CONCATENATE(IF()) - how to simplify current solution?

My data looks like this:

     ColumnName    PrimaryKey
1    ID               Y
2    JOB_NAME         N
3    JOB_DESC         N
4    START            N
5    END              N
...

I'm looking to list all columns that are primary keys - so, all columns where PrimaryKey = "Y" - in one cell, with "+" signs inbetween each returned row for ColumnName. This is the code I have so far:

=CONCATENATE((IF(C1="Y",B1,"")),(IF(C2="Y",B2,"")),(IF(C3="Y",B3,"")),(IF(C4="Y",B4,"")),(IF(C5="Y",B5,"")))

It gets the job done, but since I have over 50 rows in the ColumnName column, it ought to be a bit more concise.

Aucun commentaire:

Enregistrer un commentaire