Find Duplicate Values with in a Rollup

[Jason_Friedlan2]

I need the formula to display the duplicate values found in a rollup.

I can’t do ArrayUnique because I actually want to know what the duplicate ones are and there are too many combinations for me to code it using an if statement for each result.

Thanks!

[Ben_Young1]

Hey @Jason_Friedlander2!

This is interesting.
There might be a few ways to approach this problem.

Could you possibly share additional details about your use case and exactly what you’re trying to achieve in the context of that use case?

It would greatly help in narrowing down the scope of a solution.

[Jason_Friedlan2]

Ok I here is the best I can do:

• I have a table of time
• Table of people
• Table of locations
• I have people scheduled to an time in a location
• I want to see if any one person is booked to more than one location in any given time and raise a red flag

[Alexey_Gusev]

I think you should use arrayjoin for rollup, then add lookup of this value to the first table and use (LEN(''&{LOOKUP of ROLLUP}-LEN(SUBSTITUTE( ,VALUE_FIELD,'')))/LEN(VALUE_FIELD).
Numbers >1 = duplicates, which you can rollup back using rollup condition field

lookup is array, so you need to add that empty string to convert it to string.

I guess in some cases might be a ‘more elegant’ solution

[Alexey_Gusev]

As @Ben.Young predicted, context is important.
When I saw ‘find duplicates in rollup’, I though 'wow, that’s intertesting. how would I solve that?

but from your context, my solution is overengineerig. you just have to set count in some way, depending on how do you links reflects this:

Jason_Friedlander2:

I have people scheduled to an time in a location

I can’t say without seeing link schema, but it feels like instead of search duplicates in people-in all locations, you need to check (count of locations) for each of people to be <2.

[kuovonne]

Jason_Friedlander2:

I have people scheduled to an time in a location

A screenshot would be helpful. How is the combination of a single person at a particular time and place stored?

Do you have a single three way junction table with three linked record fields?

Is it possible (and acceptable) for someone to be booked for the same time and place multiple times?

Do you need to see the actual value of the duplicate, or is it okay if you are simply earned if there is a duplicate and you can then manually identify it?

[Jason_Friedlan2]

My timeslots has links to each event and I can lookup the people in each one.

So my timeslot table has the rollup of all people in all rooms during that time. I need to look in that rollup to find the duplicates

[kuovonne]

It sounds like you have a [Timeslots] table that is linked to an [Events] table. Each event is linked to only one time slot. Each event is also linked to multiple people.

Include a lookup of the people in the [Events] table.

Then in the [Timeslots] table, have a rollup of the lookup.

Here is the configuration of the rollup:

Here is the formula:

IF(
  COUNTALL(values) != COUNTALL(ARRAYUNIQUE(values)),
  "Duplicates: " & (COUNTALL(values) - COUNTALL(ARRAYUNIQUE(values)))
)

Note that this system will let you know how many duplicates to look for, but it will not tell you the exact name. In order to find the exact name you can look for it manually, or you can use a custom script.