Count unique entries among two columns

Rebecca Menning

I am looking for a way to create a report or a column on an existing smartsheet to count employees by unique supervisor name

Thanks for any ideas!

Becky

Rebecca Menning

Thank you for the idea! We will certainly be experimenting

EvermoreCoffee

Hi @Rebecca Menning,

I took the data you've provided and was able to get something similar to the outcome of what you are looking for, on a sheet:

I am not sure if there is a way to list the [Unique Employees] as separate rows, though we can use the CHAR(10) combined with a JOIN() formula, followed with wrapping the values in that column, to get a similar layout. Here are the formulas I used for each column:

[Supervisor Helper] (Used to assist the DISTINCT() and CONTAINS() formulas)

=JOIN([Supervisor 1]@row:[Supervisor 2]@row, ",")

[Count of Unique Employees]

=COUNT(DISTINCT(COLLECT(Employee:Employee, [Supervisor Helper]:[Supervisor Helper], CONTAINS(Supervisors@row, @cell))))

[Unique Employees]

=JOIN(DISTINCT(COLLECT(Employee:Employee, [Supervisor Helper]:[Supervisor Helper], CONTAINS(Supervisors@row, @cell))), CHAR(10))

The [Supervisors] column is just manual name entry for any supervisors.

Hope this helps!

Rebecca Menning

Thank you for the idea! We will certainly be experimenting