Skip to main content

Find duplicates in a rollup

K
  • Kelsey-
  • Participating Frequently
  • 8 replies

Hi there - I schedule shipments to be picked up on specific dates from specific sites at specific times, and I need a way to identify if I've scheduled any shipments to be picked up on the same date/site/time to make sure I'm not double booking a site. I've created a formula in my Shipments table that concatenates the three fields:

IF(
  AND({Load Date}, {Site Abbrev.}, {Load Time}),
  DATETIME_FORMAT({Load Date}, "MM/DD/YY") & "_" &
  {Site Abbrev.} & "_" &
  {Load Time},
  ""
)
 
And I've created a Rollup in my Sites table using ARRAYJOIN(values, ", "). All that seems to be working just fine. But the formula I've created to identify any duplicates in the Rollup field always returns No Duplicates even if there are duplicates. 
 
IF(
  LEN(ARRAYJOIN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}, ", ")) >
  LEN(ARRAYJOIN(ARRAYUNIQUE({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", ")),
  "Duplicates Found",
  "No Duplicates"
)
 
Here's an example of one of the rollup fields (load date_site abbreviation_load time) that contains a duplicate (11/25_AU_8 am) but is still returning No Duplicates in the formula field: 
11/19/24 _AU _10am, 11/25/24 _AU _8am, 11/25/24 _AU _8am, 11/12/24 _AU _11am, 11/25/24 _AU _10am, 11/12/24 _AU _10am, 12/03/24 _AU _10am, 11/14/24 _AU _10am
 
What am I doing wrong? I feel like I'm fighting for my life over here lol
 
Thanks,
Kelsey
 

7 replies

Ron_Daniel
  • Ron_Daniel
  • Inspiring
  • 104 replies
  • November 11, 2024

Kelsey,
I would normally test my answer and supply screenshots, but this one is a shot in the dark while I'm on the move. I think if you use ARRAYFLATTEN, then ARRAYUNIQUE will properly identify the duplicates.
Try something along the lines of
IF( LEN(ARRAYJOIN(ARRAYUNIQUE(ARRAYFLATTEN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", "))) < LEN(ARRAYJOIN(ARRAYFLATTEN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", ")), "Duplicates Found", "No Duplicates" )

Let me know how it goes!

K
  • Kelsey-
  • Author
  • Participating Frequently
  • 8 replies
  • November 11, 2024
Ron_Daniel wrote:

Kelsey,
I would normally test my answer and supply screenshots, but this one is a shot in the dark while I'm on the move. I think if you use ARRAYFLATTEN, then ARRAYUNIQUE will properly identify the duplicates.
Try something along the lines of
IF( LEN(ARRAYJOIN(ARRAYUNIQUE(ARRAYFLATTEN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", "))) < LEN(ARRAYJOIN(ARRAYFLATTEN({DEV | Load Date, Load Site, Load Time Rollup (from Shipments)}), ", ")), "Duplicates Found", "No Duplicates" )

Let me know how it goes!


No luck, still getting No Duplicates. ☹ Thanks for giving it a shot. Any other ideas? 

TheTimeSavingCo
Forum|alt.badge.img+18

Hmm I experimented a bit and got this to work:

 

 

IF( LEN(ARRAYJOIN(values)) > LEN(ARRAYJOIN(ARRAYUNIQUE(values))), "Has duplicates", "No duplicates" )

 

 


 

Link to base

Not sure how your data's set up though.  If the above didn't work could you provide some screenshots of your tables so I can try to replicate it? 

--
Edited, found a bug with the formula and fixed it!

Adam, Consultant @ The Time Saving Co
Ron_Daniel
1 person likes this
  • Ron_Daniel
    Ron_Daniel
K
  • Kelsey-
  • Author
  • Participating Frequently
  • 8 replies
  • November 12, 2024
TheTimeSavingCo wrote:

Hmm I experimented a bit and got this to work:

 

 

IF( LEN(ARRAYJOIN(values)) > LEN(ARRAYJOIN(ARRAYUNIQUE(values))), "Has duplicates", "No duplicates" )

 

 


 

Link to base

Not sure how your data's set up though.  If the above didn't work could you provide some screenshots of your tables so I can try to replicate it? 

--
Edited, found a bug with the formula and fixed it!


The problem is that I'm not using any linked fields. Here's the formula on the Shipments table that concatenates the date/site/time:

And here's the setup on the Warehouses table where I've got the Rollup from Shipments and the formula field to identify duplicates:

Thank you for your help! Feels like this should be easier. 

TheTimeSavingCo
Forum|alt.badge.img+18
Kelsey- wrote:

The problem is that I'm not using any linked fields. Here's the formula on the Shipments table that concatenates the date/site/time:

And here's the setup on the Warehouses table where I've got the Rollup from Shipments and the formula field to identify duplicates:

Thank you for your help! Feels like this should be easier. 


Hm interesting.  Could you provide a screenshot of the field settings for the 'Dev' field?  If you could DM me an invite link to an empty copy of your base that'd be easiest!

Adam, Consultant @ The Time Saving Co
J
  • Joel1
  • New Participant
  • 4 replies
  • January 5, 2025
Kelsey- wrote:

The problem is that I'm not using any linked fields. Here's the formula on the Shipments table that concatenates the date/site/time:

And here's the setup on the Warehouses table where I've got the Rollup from Shipments and the formula field to identify duplicates:

Thank you for your help! Feels like this should be easier. 


Hi, was this solved? Having the exact same issue. 🙏

K
  • Kelsey-
  • Author
  • Participating Frequently
  • 8 replies
  • January 6, 2025
Joel1 wrote:

Hi, was this solved? Having the exact same issue. 🙏


Hi there - this was eventually solved by putting this formula in the rollup field. 

IF(
  LEN(ARRAYJOIN(values)) > LEN(ARRAYJOIN(ARRAYUNIQUE(values))),
  "Has duplicates",
  "No duplicates"
)

And then by grouping the table by the date/site/time field so I could quickly see how many groups contained more than one item, thereby identifying the duplicates. See in the screenshot how the 01/08/25_Citi - YD_10am group has two items in it. 

Hope that helps! 

 

Kelsey

Reply


Most helpful members this week

Terms & Conditions