Create a list in one column from multiple columns

jrlschumacher

I'm trying to create a list in one column "Needs" anything from the previous columns that is missing. I've figured out I should be using the =COLLECT(JOIN) function but can't figure out how to incorporate the conditional aspect. IE the first row is missing three items so "Immunizations", Open "Enrollment", "Screening" would populate into the "Needs Column.

AKnight

Hi @jrlschumacher !

I would use a combination of IF and ISBLANK statements like the following:

=IF(ISBLANK([BC?]@row), "BC", "") + ", " + IF(ISBLANK([IMM]@row), "Immunizations", "") + ", " + IF(ISBLANK([OE?]@row), "Open Enrollment", "") + ", " + IF(ISBLANK([SCRN]@row), "Screening", "")

The comma between each statement will allow it to read as a list rather than a run on word.

Hope this helps!

Paul Newcome

You would need a series of IF statements strung together.

=IF([BC?]@row = "", "BC, ", "") + IF(IMM@row = "", "Immunizations, ", "") + IF([OE?]@row = "", "Open Enrollment, ", "") + IF([SCRN?]@row = "", "Screening", "")

jrlschumacher

THANK YOU! that was quick!