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?

screenshot-2tablescollect.GIF

Tags:

Comments

  • Isaac Jose
    Isaac Jose Employee

    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, [email protected])), "; ")

    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

  • This worked great!! Many thanks!!

  • 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
    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, [email protected])))

    thinkspi.com

  • 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
    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.

    thinkspi.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 

    thinkspi.com