Trying to copy a multi select cell data into another cell once criteria is met

toni.toni601

If X is true, copy cells in Y

What formula should I use for this?

Keep in mind, cell Y is a multi select cell.

Genevieve P.

Hi @toni.toni601

Thanks for providing more information! It sounds like you want to evaluate an entire column, not individual rows, and that you're building this formula in a second sheet (not the current sheet with the columns).

In this instance, you could use a JOIN formula to join together all of your customers, and then use COLLECT to filter down by size. See: Formula combinations for cross sheet references

For example:

=JOIN(COLLECT({Business customers}, {your size}, "XS"), ", ")

Notice at the end I have a comma to separate your values in the JOIN function. However since you noted your column is multi-select, you'll want to build the formula in a multi-select column as well and use CHAR(10) as the separating character, like so:

=JOIN(COLLECT({Business customers}, {your size}, "XS"), CHAR(10))

If this didn't help, it would be useful to see screen captures of your source sheet, but please block out sensitive data.

Cheers,

Genevieve

Paul Newcome

@toni.toni601 You would use a DISTINCT function wrapped around the collect.

=JOIN(DISTINCT(COLLECT(................................)), CHAR(10))

Paul Newcome

You would use this in another multi-select column:

=IF([Column X]@row = "TRUE", [Column Y]@row)

toni.toni601

In column X, I have size XS. In column Y I have customers: Paul, Jack, Jane.

I want the automation to check my size column for all the XS and only list Paul, Jack, Jane in the cell.

The formula you gave me, didn't work... =IF([{your size}]@row = "XS", [{Business customers}]@row)

This formula resulted in #unparseable

any other ideas?

Genevieve P.

Hi @toni.toni601

Thanks for providing more information! It sounds like you want to evaluate an entire column, not individual rows, and that you're building this formula in a second sheet (not the current sheet with the columns).

In this instance, you could use a JOIN formula to join together all of your customers, and then use COLLECT to filter down by size. See: Formula combinations for cross sheet references

For example:

=JOIN(COLLECT({Business customers}, {your size}, "XS"), ", ")

Notice at the end I have a comma to separate your values in the JOIN function. However since you noted your column is multi-select, you'll want to build the formula in a multi-select column as well and use CHAR(10) as the separating character, like so:

=JOIN(COLLECT({Business customers}, {your size}, "XS"), CHAR(10))

If this didn't help, it would be useful to see screen captures of your source sheet, but please block out sensitive data.

Cheers,

Genevieve

Paul Newcome

Also note that you should use either curly brackets for a cross sheet reference or square brackets for referencing a column on the same sheet as the formula. You should not use both which is why your IF statement is throwing that particular error.

Genevieve P.

Thanks, @Paul Newcome! 🙂 Good catch.

toni.toni601

That worked but how do I remove the duplicates?

Paul Newcome

@toni.toni601 You would use a DISTINCT function wrapped around the collect.

=JOIN(DISTINCT(COLLECT(................................)), CHAR(10))

toni.toni601

You all are the GREATEST