COLLECT – IF – DISTINCT Challenge

alex44

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?

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

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?

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!