Unique array combining arrays from 2 rollup fields

[Kat_Levine]

I have 2 rollup fields in a table that are formatted to show ARRAYUNIQUE(values). I am trying to create a formula column that combines these values in an array, and only displays unique values. I was trying to use: ARRAYUNIQUE({Rollup1},{Rollup2}), but am only getting the values from Rollup1 to show up. Help?

[Jeremy_Oglesby]

I think you’ll need a field in between that concatenates the two arrays first, then your field that eliminates duplicates.

So the formula field in between would look like this:

CONCATENATE(
   {Rollup 1},
   ", ",
   {Rollup 2}
)

That should combine the two arrays from the rollups into a string that looks like an array. The ", " in the middle is to add a comma-space after the last entry in {Rollup 1} and before the first entry in {Rollup 2}.

Now you can make another formula field that does this:

ARRAYUNIQUE( {Combined Rollups}, ", " )

I didn’t test that, so let me know if it doesn’t work and we can try something else.

[Kat_Levine]

In the CONCATENATE field, I’m getting extra ", "s at the beginning if Rollup 1 is empty, extra ", "s at the end if Rollup 2 is empty, and if there are multiple values in a Rollup, the ", " that was separating them in the rollup is being lost in the Concatenate field.

The ARRAYUNIQUE field isn’t filtering duplicates, possibly because of the syntax in the Concatenate field. Anything else to try?

[Jeremy_Oglesby]

hmm… well, the extra comma would be an easy fix, but I didn’t realize that CONCATENATE() would strip out the commas that were separating the array values.

Kat_Levine:

The ARRAYUNIQUE field isn’t filtering duplicates, possibly because of the syntax in the Concatenate field.

Ya, you got that right. It’s got to be because of the missing/extra commas.

I’ll think about this one a bit more.

Question - are the two Rollup fields referencing the same table, or different tables?

[Kat_Levine]

The Rollup fields are both referencing the same table.

[Jeremy_Oglesby]

:frowning: This is proving to be a lot more difficult than I thought it would be at first glance…
CONCATENATE() behaves pretty strangely when passed an array.

[Jeremy_Oglesby]

A couple more questions, @Kat_Levine…

1) What type of data is being rolled up? (strings, numbers, dates?)
2) In the table that the rollups live in, are you linking all records from the first table to a single record, or are there many records that are linked across both tables?
3) Is the final unique list the only thing you are after, or is it mandatory that the first two rollups also contain only unique values? (ie, can we shift the removal of all the duplicates to the last step, rather than removing duplicates in two steps?)

EDIT
Nevermind about the questions, Kat. No matter what I’ve tried, I can’t get Airtable to perform ARRAYUNIQUE() outside of a Rollup field. Which means using a formula that operates after the Rollups is not an option.

I tried to think of ways to combine the data prior to the Rollup in a way that still allows the rollup to see each value as a separate piece of data, but with no luck so far.

Sorry – looks like this may not be possible.

[Kat_Levine]

:slightly_frowning_face: Thank you for following up.

[Enrique_Torrebl]

Hi, not sure if you’re still interested in getting around this issue, but I found a way of getting this done by utilizing Zapier and using its JavaScript action to combine the rolled up arrays and basically manipulate it in any way you like and then send an array back to Airtable. It is a bit involved so If you’re still interested let me know and I’ll put together a more detailed guide.

[Jason_Calaiaro]

I realize it’s been a little while, but I’d be interested in your solution, Enrique. I’m having same issue.