Distinct date range for users

[Steve Saunders]

Hi all,

I have over 600 rows of user data and am looking for a way to determine the first distinct date range for each user. In the example sheet, Sally has three dates all of which are the same, but I only want the first to be marked as distinct. Eduarado has only one so this is distinct, and Jimmy has two dates and the first is to be marked as distinct. I want to determine this for each row and user with a “Y/N" result so I can then perform further calculations.

I can get a listing of distinct values from the dates in column B and C, but can't use the username as an additional criterion. Does anyone have suggestions as to where I could look?

Regards,
Steve

Sample.xlsx
	A	B	C	D
1	User	Start	Finish	Is distinct date?
2	Sally	13/08/2023	14/02/2023	Y
3	Eduarado	1/01/2022	18/10/2022	Y
4	Jimmy	2/02/2023	8/08/2023	Y
5	Sally	13/08/2023	14/02/2023	N
6	Sally	13/08/2023	14/02/2023	N
7	Jimmy	2/02/2023	8/08/2023	N
Sheet1

 

[awoohaw]

If you have no way of distinguishing which row belongs to which name then you cannot get distinct by name unless you use the name.
Can you give more details on your scenario as to why you cannot use values of another column to distinguish one date from another?
The only thing that could make it possible without using the name is that there is only one name with a date. In other words it is not possible in your scenario for Jimmy to have a date of 13/08/2023 - if this is true then you just need to get unique values of each date. UNIQUE($B$2:$B$7)

 

[Peter_SSs]

Like this?

23 11 07.xlsm
	A	B	C	D
1	User	Start	Finish	Is distinct date?
2	Sally	13/08/2023	14/02/2023	Y
3	Eduarado	1/01/2022	18/10/2022	Y
4	Jimmy	2/02/2023	8/08/2023	Y
5	Sally	13/08/2023	14/02/2023	N
6	Sally	13/08/2023	14/02/2023	N
7	Jimmy	2/02/2023	8/08/2023	N
Distinct

Cell Formulas
Range	Formula
D2:D7	D2	=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,"Y","N")

If you just wanted the list elsewhere ..

23 11 07.xlsm
	A	B	C						I	J	K	L
1	User	Start	Finish							User	Start	Finish
2	Sally	13/08/2023	14/02/2023							Sally	13/08/2023	14/02/2023
3	Eduarado	1/01/2022	18/10/2022							Eduarado	1/01/2022	18/10/2022
4	Jimmy	2/02/2023	8/08/2023							Jimmy	2/02/2023	8/08/2023
5	Sally	13/08/2023	14/02/2023
6	Sally	13/08/2023	14/02/2023
7	Jimmy	2/02/2023	8/08/2023
8
Distinct

Cell Formulas
Range	Formula
J1:L4	J1	=UNIQUE(A1:C7)
Dynamic array formulas.

 

[Steve Saunders]

Like this?

23 11 07.xlsm
	A	B	C	D
1	User	Start	Finish	Is distinct date?
2	Sally	13/08/2023	14/02/2023	Y
3	Eduarado	1/01/2022	18/10/2022	Y
4	Jimmy	2/02/2023	8/08/2023	Y
5	Sally	13/08/2023	14/02/2023	N
6	Sally	13/08/2023	14/02/2023	N
7	Jimmy	2/02/2023	8/08/2023	N
Distinct

Cell Formulas
Range	Formula
D2:D7	D2	=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)=1,"Y","N")

If you just wanted the list elsewhere ..

23 11 07.xlsm
	A	B	C						I	J	K	L
1	User	Start	Finish							User	Start	Finish
2	Sally	13/08/2023	14/02/2023							Sally	13/08/2023	14/02/2023
3	Eduarado	1/01/2022	18/10/2022							Eduarado	1/01/2022	18/10/2022
4	Jimmy	2/02/2023	8/08/2023							Jimmy	2/02/2023	8/08/2023
5	Sally	13/08/2023	14/02/2023
6	Sally	13/08/2023	14/02/2023
7	Jimmy	2/02/2023	8/08/2023
8
Distinct

Cell Formulas
Range	Formula
J1:L4	J1	=UNIQUE(A1:C7)
Dynamic array formulas.

Exactly like this Peter - thank you so much! The step I was missing was the intermediary calculation (for lack of a better term) of "=1" and I'd never have thought of it!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, if you didn't want to copy the formula down the column you could get the values like this.
Longer formula - but it only needs to go in the top cell and not copied down.

23 11 07.xlsm
	A	B	C	D
1	User	Start	Finish	Is distinct date?
2	Sally	14/08/2027	15/02/2027	Y
3	Eduarado	2/01/2026	19/10/2026	Y
4	Jimmy	3/02/2027	9/08/2027	Y
5	Sally	14/08/2027	15/02/2027	N
6	Sally	14/08/2027	15/02/2027	N
7	Jimmy	3/02/2027	9/08/2027	N
Distinct

Cell Formulas
Range	Formula
D2:D7	D2	=BYROW(A2:C7,LAMBDA(rw,LET(a,INDEX(rw,1),b,INDEX(rw,2),c,INDEX(rw,3),IF(COUNTIFS(A$2:a,a,B$2:b,b,C$2:c,c)=1,"Y","N"))))
Dynamic array formulas.