Double Arrayunique

Airtable’s array functions only work on arrays generated by other fields. I haven’t found a way to create an array from scratch, even though the array function reference makes it appear that that’s possible.

If you were using ARRAYUNIQUE on {Email copy} alone, it would work (and by “work” I mean that it would see {Email copy} as an array, and operate on it as such). In your formula, though, you’re concatenating that array with a string from the {Email} field, which generates an output string as a result. ARRAYUNIQUE no longer has an array to work with, which is why it’s not removing the duplicate email addresses.

On a side note, the reason that the commas from {Email copy} don’t appear in your formula output is because the commas you see in that rollup field are only there for display clarity. When turning an array into a string, Airtable just mashes all the array items together without any separators.

If your goal is to pull {Email} out of the {Email copy} array if it’s there, the formula below will work, though the end result will still be a string, so that may affect any other processing you want to do with the email addresses elsewhere.

Email & ", " &
IF(
    FIND(
        Email,
        "" & {Email copy}
    ),
    SUBSTITUTE(
        ARRAYJOIN(
            {Email copy},
            ", "
        ),
        Email & ", ",
        ""
    ),
    ARRAYJOIN(
        {Email copy},
        ", "
    )
)

This will only remove {Email} if it’s found in {Email copy} (Test 1 record). Otherwise it will leave it alone (Test 2 record).

Thank You again so much Justin,
this partly works and has some fascinating side effects.
First I removed the & ", " after the Substitute old text value (EMail), because for some reason this didn’t work with me.

Now it works, but only if the E-mail in Email copy is standing alone or the second value of the array.
It doesn’t work if there are multiple values and the E-Mail to substitute is the first one.

I checked and it seems like for some reason he can’t find the string if it’s the first part.

Now that got me trying and I figured out that the check for whether the string is in Email copy is not necessary, as the substitute function actually finds the string or ignores it.

So I left it with this

IF(EMail="", “”, EMail & ", ") & SUBSTITUTE(ARRAYJOIN({Email copy}, ", "), EMail, “”)

And it seems to work perfectly.

Thank You so much for this, couldn’t have done it without you!