COLLECT – IF – DISTINCT Challenge

I want to JOIN(COLLECT) unique, distinct instances of a date given values in other columns. I have a history log of participant registration dates. The participant can register more than one time on a given date. I want to JOIN(COLLECT) all of the unique registration dates an individual registers. Is this possible?

Table 1 has what I already have – a history of all registration instances. Table 2 is a separate table, where I want the data collected (red text is what I’m missing). Thoughts?

Find more posts tagged with

Formulas

Comments

Isaac Jose

Hi Alex,

Thanks for your post, I was able to achieve this by nesting my COLLECT inside my DISTINCT function. See below:

=JOIN(DISTINCT(COLLECT([Registration Date Log]:[Registration Date Log], Participant:Participant, Participant@row)), "; ")

The above formula will COLLECT the dates associated with the participant on the row. Then the DISTINCT function will remove any duplicates that appear in the COLLECT.

I hope this helps!

Isaac J

Smartsheet Support

Join distinct collect.PNG

alex44

This worked great!! Many thanks!!

alex44

Now I'm trying to build a *second* column to COUNT the number of unique dates by participant. See desired new column and results in red in the screenshots. I've looked at a couple ways to figure it out but it's not working. Any thoughts?

count unique dates.png

Paul Newcome

You should be able to just replace the JOIN function with a COUNT function (and of course remove the delimiter).

=COUNT(DISTINCT(COLLECT([Registration Date Log]:[Registration Date Log], Participant:Participant, Participant@row)))

alex44

Thanks, Paul. I tried that and somehow it's only counting 1 on all rows (not "2" on one of the rows as expected). I'll keep at it.

Paul Newcome

That's odd. It worked for me when I tested. I literally took the same exact JOIN formula, replaced "JOIN" with "COUNT" and then removed the delimiter section.

alex44

Thanks, Paul! It works now - not sure where I goofed up before. Thanks!!

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…