Counting Unique Values

[errollflynn]

Hello everyone,

I have a large data set, almost 100k lines, with payroll related data. In addition to employee information, each row has hours worked in a specific pay period, the starting and ending dates of the pay period, and the date the check was issued. In some instances an employee will have worked in multiple locations during one pay period and will have two checks on the same pay date and hence will have two rows with the same pay date. I am trying to find a way to determine how many weeks an employee has worked over the course of the entire data set.

Initially I thought I could use a combination of the pay date, a unique key for the employee and the unique and filter functions to count the unique instances of the pay date in conjunction with the key. I was not able to successfully write the formula and even if I was, I was not sure how to apply it to the entire workbook. I've attached a sample data set and would greatly appreciate any suggestions.

 

[AhoyNC]

Maybe something like this:

Book1
	A				E	F	G	H	I
1	Key					Starting Pay Period		Unique Key	# Weeks
2	LNAME20001					4/24/2022		LNAME20001	4
3	LNAME20001					4/17/2022		LNAME20002	15
4	LNAME20001					4/10/2022
5	LNAME20001					4/3/2022
6	LNAME20002					11/13/2022
7	LNAME20002					11/13/2022
8	LNAME20002					11/6/2022
9	LNAME20002					10/30/2022
10	LNAME20002					10/23/2022
11	LNAME20002					10/9/2022
12	LNAME20002					10/2/2022
13	LNAME20002					9/25/2022
14	LNAME20002					9/18/2022
15	LNAME20002					9/4/2022
16	LNAME20002					8/21/2022
17	LNAME20002					7/10/2022
18	LNAME20002					7/10/2022
19	LNAME20002					6/19/2022
20	LNAME20002					6/5/2022
21	LNAME20002					5/29/2022
22	LNAME20002					5/8/2022
23	LNAME20002					5/8/2022
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=UNIQUE(A2:A23)
I2:I3	I2	=COUNT(UNIQUE(FILTER($F$2:$F$23,$A$2:$A$23=$H2)))
Dynamic array formulas.

 

[errollflynn]

Maybe something like this:

Book1
	A				E	F	G	H	I
1	Key					Starting Pay Period		Unique Key	# Weeks
2	LNAME20001					4/24/2022		LNAME20001	4
3	LNAME20001					4/17/2022		LNAME20002	15
4	LNAME20001					4/10/2022
5	LNAME20001					4/3/2022
6	LNAME20002					11/13/2022
7	LNAME20002					11/13/2022
8	LNAME20002					11/6/2022
9	LNAME20002					10/30/2022
10	LNAME20002					10/23/2022
11	LNAME20002					10/9/2022
12	LNAME20002					10/2/2022
13	LNAME20002					9/25/2022
14	LNAME20002					9/18/2022
15	LNAME20002					9/4/2022
16	LNAME20002					8/21/2022
17	LNAME20002					7/10/2022
18	LNAME20002					7/10/2022
19	LNAME20002					6/19/2022
20	LNAME20002					6/5/2022
21	LNAME20002					5/29/2022
22	LNAME20002					5/8/2022
23	LNAME20002					5/8/2022
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=UNIQUE(A2:A23)
I2:I3	I2	=COUNT(UNIQUE(FILTER($F$2:$F$23,$A$2:$A$23=$H2)))
Dynamic array formulas.

Worked like a charm! Thank you!

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Peter_SSs]

If you are using a spill range with a formula in the top cell inly to get the Unique key list, then perhaps it makes sense to do the same with the 3 Weeks rather than copy the formula down?

23 06 06.xlsm
	A				E	F	G	H	I
1	Key					Starting Pay Period		Unique Key	# Weeks
2	LNAME20001					24/04/2022		LNAME20001	4
3	LNAME20001					17/04/2022		LNAME20002	15
4	LNAME20001					10/04/2022
5	LNAME20001					3/04/2022
6	LNAME20002					13/11/2022
7	LNAME20002					13/11/2022
8	LNAME20002					6/11/2022
9	LNAME20002					30/10/2022
10	LNAME20002					23/10/2022
11	LNAME20002					9/10/2022
12	LNAME20002					2/10/2022
13	LNAME20002					25/09/2022
14	LNAME20002					18/09/2022
15	LNAME20002					4/09/2022
16	LNAME20002					21/08/2022
17	LNAME20002					10/07/2022
18	LNAME20002					10/07/2022
19	LNAME20002					19/06/2022
20	LNAME20002					5/06/2022
21	LNAME20002					29/05/2022
22	LNAME20002					8/05/2022
23	LNAME20002					8/05/2022
Unique week count

Cell Formulas
Range	Formula
H2:H3	H2	=UNIQUE(A2:A23)
I2:I3	I2	=BYROW(H2#,LAMBDA(rw,COUNT(UNIQUE(FILTER(F2:F23,A2:A23=rw)))))
Dynamic array formulas.