COLLECT, Criterion NOT BLANK

Art Schneiderheinze

Hello:

I'm using a JOIN to collect values from another sheet. The range includes two columns on the other sheet. I am collecting the first column, so long as the first column contains a string and the second column is not blank.

I have this but it isn't working:

JOIN(COLLECT({PB-CMR-Master}, {PB-CMR-URL}, CONTAINS(<>"", @cell), {PB-CMR-Master}, CONTAINS([CUR-MOD]@row, @cell)), " / ")

{PB-CMR-Master} is the first column, which I am collecting, if it contains a string [CUR_MOD}@row

{PB-CMR-URL} is the second column in the other sheet, which should NOT be blank, to collect the first column

I am not sure if I set up the criterion that evaluates the second column to NOT be blank.

Art Schneiderheinze

Think I figured it out:

JOIN(COLLECT({PB-CMR-Master}, {PB-CMR-URL}, NOT(ISBLANK(@cell)), {PB-CMR-Master}, CONTAINS([CUR-MOD]@row, @cell)), " / ") 

Genevieve P.

Hi @Art Schneiderheinze

I'm glad you figured it out! Thanks for posting your solution.

PeggyLang

@Genevieve P.

I have a form with numerous logic criteria.

If select Door then there are further options.

If select Fire-Life-Safety then there are further options.

If select HVAC then there are further options . . . and so on.

In my worksheet I want 'Request Code' to be a collection of anything from 'Request' Codes thru to 'Roof'

In example above I really only want 'Request Code' to illustrate 'HVAC-Odor', but I am getting all of the delimiters of the empty cells between 'Request Codes' and 'HVAC'.

Is there a way to 'skip' the empty cells? (so I don't get a collection of '-''s?)

Genevieve P.

Hey @peggy lang

Yes! We can use the same format as the formula above, with JOIN(COLLECT, specifying the cells in your range need to be "not blank", like so:

=JOIN(COLLECT([Request Codes]@row:[Roof]@row, [Request Codes]@row:[Roof]@row, <>""), "-")

Cheers,

Genevieve