Join IF Contact Type = Customer

JSpears

I have a sheet with the following columns:

Contact Type | Email

In the Sheet Summary, I have a field where I would like to create an email string that can easily be used to paste a bunch of email separated by a semi-colon for easy sharing.

I want to join all emails where Contact Type is "Customer" into a single field with the email separated by a ";" and if the email field is blank, I want it ignored.

Not sure if I would set this up as an IF with a JOIN or a JOIN with an IF.

I have this so far, but it's not working: =IF(AND([Contact Type]@row = "Customer", email@row <> ""), JOIN(Email:Email, " ;"))

Thank you in advance

JamesB

@JSpears

Maybe something like this?

=JOIN(COLLECT(Email:Email, [Contact Type]:[Contact Type], ="Customer", Email:Email, <>""), ";")

JamesB

@JSpears

Try This:

=(IF(AND([Contact Type]@row = "Customer", Email@row <> ""), [Contact Type]@row + ";" + Email@row))

JSpears

@JamesB - that gave me #unparseable

JamesB

sorry, I realized that I have a couple of extra parentheses.

=IF(AND([Contact Type]@row = "Customer", Email@row <> ""), [Contact Type]@row + ";" + Email@row)

I just tested this in a third column and it is working, but only on a per row basis. Based on what I am reading in your original post, you are looking to collect all of the cells that meet these criteria into one cell?

JamesB

@JSpears

Maybe something like this?

=JOIN(COLLECT(Email:Email, [Contact Type]:[Contact Type], ="Customer", Email:Email, <>""), ";")

Paul Newcome

You will need a JOIN/COLLECT combo for this:

=JOIN(COLLECT(Email:Email, Email:Email, @cell <> "", [Contact Type]:[Contact Type], @cell = "Customer"), "; ")