Identify/Count DISTINCT Names per date

Jennifer Lenander

Employees can provide multiple responses each day if they want. But I only want to count a name once each day. So in the below, employee # 12345 submitted two responses on 12/16. In the 'Count' column, I only want the box checked for one of their submissions on 12/16. Essentially I'm looking for unique employee #s each day. I have tried a couple things and think I'm close but I'm not sure what I'm doing wrong.

=IF(MATCH([Employee #]@row, [Employee #]:[Employee #], 0) = MATCH(Date@row, Date:Date, 0), 1, 0) - this checks the box of the first two boxes but none of the rest.

=IF(MATCH([Employee #]@row, [Employee #]:[Employee #], 0) <> MATCH(Date@row, Date:Date, 0), 1, 0) - this checks the box of the last 5 rows but neither of the first 2.

What am i missing?

Leibel S

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

You count column should be:

=IF(MIN(COLLECT([ROW#]:[ROW#],[Employee #]:[Employee #],[Employee #]@row,Date:Date,Date@row))=[ROW#]@row,1)

This will check off the first time the employee name shows up on that date.

Jennifer Lenander

I had the LINE ID in there but couldn't figure out how to use it in this scenario. THANK YOU!!!!!

The above works but I don't understand the need for the second helper column. Can't you just use the LINE ID in the count formula? The Row# column seems duplicate? It seems to work without the row# but maybe there is a reason to have that column that I'm not seeing or thinking of?

Leibel S

Yes, you could use it without the ROW#.

If you specifically wanted to check off the first one submitted then you need the ROW#...

This process is the same I use for finding duplicates where I want flag specifically the second row as a duplicate entry.

I just default to doing it this way...

Jennifer Lenander

I got it. For me, it doesn't matter if it marks the first one or not, just need it to mark one. This is a pretty handy little trick. Thanks!