Identify/Count DISTINCT Names per date

Options

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?



Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    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
    Jennifer Lenander ✭✭✭✭✭
    Options

    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
    Leibel S ✭✭✭✭✭✭
    Options

    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
    Jennifer Lenander ✭✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!