Star/Check only rows that are unique/not blank

Mspencer

I'm trying to count the number of unique attendees for a training session on a registration form by pulling data from both the registration form AND the roster for the specific session's date.

My current formula is

=if(countifs({08/08 DC Registrant},[Email Address]@row,{08/08 DC Attended},@cell=1)>0,1)

and it works, HOWEVER, if an attendee has registered multiple times, it is starring/checking the box for every one of their rows instead of one, which results in an inaccurate/higher attendee count. Total count/starred boxes should be 11; it is currently 14.

How can I star/check only ONE row for each duplicate attendee? See screen shot. AJ and Paige are my duplicates.

I have tried inserting the following into the working formula above but none have worked:

(distinct([Email Address]:[Email Address])

([Difficult Conversations]:[Difficult Conversations],<>""

(not(isblank([difficult conversations]:[difficult conversations]))

Darren Mullen

@Mspencer

My first question is can you correct the reason why there are duplicates in the first place?

If not, it looks like you can check to see if there is no date in the "Difficult Conversations" column. Does this formula work for you?

=if(and(countifs({08/08 DC Registrant},[Email Address]@row,{08/08 DC Attended},@cell=1)>0, isdate([Difficult Conversations]@row)) ,1)

Mspencer

@Darren Mullen I wish, but the duplicates are because the spreadsheet is being fed from a registration form, so those duplicates are the same person registering multiple times to change their session dates or add more sessions. So I need to keep them.

Your formula is close but it's not checking the boxes/starring those who attended. Is another way?