Skip to main content

Is it possible to identify if there are any duplicates in a lookup array? 

I have 3 tables, all linked together: 

  • Entities (companies)
  • Meetings
  • Months

Every Meeting is linked to a month (for various uses) and to an Entity. 

From the Entities table, I have data as such - the linked meetings and a lookup of their linked months. 

What I'd like to do is identify if a month appears twice in any row. 

I don't want to eliminate duplicates (i.e. ARRAYUNIQUE). Instead I need to see if 2 or more meetings occurred in the same month for any company. 

Is there any way to do that? 

Hi,
in meetings, you have linked field to entities. add lookup to it, with {Entity name}. It will repeat linked field, but in text form, comma-separated.
Now in month table, create 2 rollup fields with just created lookup. One with ARRAYUNIQUE(values), other - ARRAYJOIN(values, ','). If their values are different, month used twice or more. The company name will be repeated in ARRAYJOIN field.
You can create 1 field instead of 2, with 

ARRAYJOIN(values, ",")!=ARRAYUNIQUE(values)

 "1" will identify duplicates, others will be "0"

This is interesting. So if I'm understanding, this will compare the Length of both the ARRAYJOIN and ARRAYUNIQUE array and tell me if there's a difference.

Would I be able to tell which linked records are the duplicates? Not just a True/False if there is some duplicate.

FYI, you have to modify the ARRAYUNIQUE rollup to match your separator

ARRAYJOIN(ARRAYUNIQUE(values), ", ")

Even though visually this looks the same without the ARRAYJOIN, there seems to be some distinction where you can't compare it using an = formula... it won't match.

FYI, you have to modify the ARRAYUNIQUE rollup to match your separator

ARRAYJOIN(ARRAYUNIQUE(values), ", ")

Even though visually this looks the same without the ARRAYJOIN, there seems to be some distinction where you can't compare it using an = formula... it won't match.


Nope, despite it looks like comma and space, it just a visual representation of Unique array.
It works for comma only

 

Reply


Terms & Conditions