Removing duplicates from a string

[Mar_Castro]

Hi all!!
I have a list of participants in a project, but as this list of participants is a lookup from several tasks linked to the project, it has duplicated items. I want to count the number of unique participants.

I’ve tried transforming this list of participants into a string, I know how to count the number of participants in that string but I need to remove the duplicates. Does anybody know how to remove duplicates from a string?

I know how to remove completely a participant from the string (using SUBSTITUTE), but I don’t know how to make it appear only once.

SUBSTITUTE({Participants}, Find, “”)

(Find is the name of the table with the name of the participant I want to remove)

Thanks in advance!!!

[ScottWorld]

Use this formula:

ARRAYUNIQUE({Your Lookup Field Here})

[Mar_Castro]

Thanks ScottWorld, I’ve tried that one, but is not working. Each task has several participants attached to it, so the arrayunique formula reviews the complete list of participants for each task, and if the complete list is different from one task to another, all of them are included.

For instance, the project has two tasks. Task 1 has Person 1, Person 2 and Person 3. And task 2 has Person 1 and Person 2.
When I use arrayunique, it compares “Person 1, Person 2, Person 3” with “Person 1, Person 2”. As they are different the final string is “Person 1, Person 2, Person 3, Person 1, Person 2”.

And I don’t know how to avoid this…

[Mar_Castro]

First of all, sorry as I’m new with Airtable and perhaps I’m not using the proper terminology. I’ll do my best to explain myself. This is not a screenshot from my actual system, but I hope it helps me explain my problem.

I have a table of employees, a table of tasks, a table of projects and a junction table to join a Project (single link) with a task (single link) that also allows me to assign several Workers (multiple link to Employee table).

In this example, Captain America is assigned to the task of collecting the infinity gems (with other colleagues), but also to the task of returning them.

I want to see which employees are assigned to each project. So I’ve created a Lookup field in the “Project” Table.
As the same worker can be included in several tasks in the same project, when I create the lookup field, they appear duplicated.

Either using Lookup or Rollup (with the formula “Arrayunique”), Captain America appears duplicated. And if I try to count the number of workers it shows 5 people, when only 4 different workers are involved.

Arrayunique is useful in case there are several tasks with the exact same workers assigned on them. But if there are several workers and only some of them are duplicated, Arrayunique considers they are two different arrays, and shows all the workers, including duplicated ones. Let me explain this better.

I’ve added a new task “Back to normality”. Only Nick Fury is assigned to this task.

As Nick Fury is also the only worker assigned to “Call Captain Marvel”, Rollup realizes is a duplicated record and doesn’t show it, although Lookup is still adding it.

So my question is, how can I eliminate duplicates when there are several workers assigned to a task?

I hope is more clear now :grinning_face_with_big_eyes:
Thanks!!!

[ScottWorld]

I like the superhero example! :stuck_out_tongue_closed_eyes:

Try this:

Instead of using a Lookup field, change that lookup field to a rollup field and use this formula for the rollup field:

ARRAYUNIQUE(ARRAYFLATTEN(values))

That should give you an array of unique people.

To get a count of the unique people, you could modify that rollup field (or just create a brand new rollup field) using this formula:

COUNTA(ARRAYUNIQUE(ARRAYFLATTEN(values)))

Let me know if that works!

[Mar_Castro]

Sorry, still not working :frowning:

Captain America is still duplicated, and Counta() function is counting 3 :open_mouth:
I think is because Black Widow, Captain America and Hulk, as they are assigned to the same task “Collect Infinity Gems” are counting as 1.

[ScottWorld]

Hmm, very strange. Yeah, I just tried it on my end too (I was just typing it up from memory before), and it doesn’t work at all.

The ARRAYFLATTEN function isn’t actually flattening the array at all!

And on my end, ARRAYFLATTEN doesn’t even show up as a choice in the Rollup formula box.

I’m not really sure how to solve this dilemma. Perhaps someone else knows the answer, like @Kamille_Parks or @Justin_Barrett or @kuovonne or @Bill.French.

[Bill_French]

ScottWorld:

COUNTA(ARRAYUNIQUE(ARRAYFLATTEN(values)))

I’m not skilled in this, but is it possible this should be reversed?

COUNTA(ARRAYFLATTEN(ARRAYUNIQUE(values)))

[kuovonne · Accepted solution]

Using ARRAYFLATTEN can be tricky. When using it in a rollup to count unique items across lin with linked records, you need to use it in a rollup of a lookup, not a rollup of a linked record field.

ARRAYUNIQUE(ARRAYFLATTEN(values))

I tried to search these forums for the thread where this particular use case was explained to me to give credit, but I couldn’t find it.

Note that ARRAYUNIQUE(ARRAYFLATTEN(features)) works differently, depending on what type of field is being rolled up.

[ScottWorld]

Oh wow, thanks so much, @kuovonne! What an incredible revelation!! :slightly_smiling_face: Yep, that fixed it for me! :slightly_smiling_face:

This is extremely unintuitive & unexpected behavior to me!

@Mar_Castro To make this work, you have to go back into your OTHER table, and create an additional lookup field. This is, in essence, a redundant field, but as @kuovonne has pointed out, this is required to make this work. You’ll be creating a lookup field that is based on your linked record field, but it is looking up the exact same field as your linked record field. Then, you can come back to your main table, and make your rollup point to that new lookup field that you just created.

Thank you, @kuovonne, for straightening out all of this craziness! :crazy_face: