Detecting Changes in a Column for a Given ID

[ccecil02]

Hi all.

So I have a list of Employee IDs in column A and Dates in Column B. How would I go about writing a formula to tell me when a date is unique for a given ID.

For example. Employee ID 1091 has 5 dates associated with it in Column B, but only 2 are unique, how would I go about calling out those two dates?

Thanks in advance, you all are always so helpful.

 

[DanteAmor]

Put the following formula in column c


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]=Countifs($A$2:$A$11,A2,$B$2:$B$11,B2)[/TD]
[/TR]
</tbody>[/TABLE]


Copy the formula to the last row with data

The results with 1 when a date is unique for a given ID.

--

To obtain the unique dates by ID, you can create a dynamic table with the ID and date columns.

Look at the image:

https://www.dropbox.com/s/j9w0151mdhtmzkm/td 1.jpg?dl=0

 

[jtakw]

Hi,

I'm not 100% sure I understand your requirements...

For my sample below, use C1 formula to indicate All unique Dates for All unique IDs,
OR, use D1 formula to indicate All unique Dates for a Single particular ID as User Input in E2, you can also hard-code this in the formula, but having E2 is more flexible as you can change E2 value to whichever ID you want to check.

Book1
	A	B	C	D	E
1	1091	1/1/2019			ID to look
2	1091	1/2/2019	1/2/2019	1/2/2019	1091
3	1092	1/1/2019
4	1092	1/2/2019	1/2/2019
5	1092	1/1/2019	1/1/2019
6	1091	1/1/2019
7	1091	1/4/2019	1/4/2019	1/4/2019
8	1091	1/1/2019	1/1/2019	1/1/2019
Sheet457

Cell Formulas
Range	Formula
C1	=IF(COUNTIFS(A$1:A1,A1,B$1:B1,B1)=COUNTIFS(A$1:A$8,A1,B$1:B$8,B1),B1,"")
D1	=IF(A1<>E$2,"",IF(COUNTIFS(A$1:A1,E$2,B$1:B1,B1)=COUNTIFS(A$1:A$8,E$2,B$1:B$8,B1),B1,""))

 

[jtakw]

Based on the same assumption as my post above, these "shorter" versions of my formula will give the same results in a different order:

Same descriptions as above also:

Book1
	A	B	C	D	E
1	1091	1/1/2019	1/1/2019	1/1/2019	ID to look
2	1091	1/2/2019	1/2/2019	1/2/2019	1091
3	1092	1/1/2019	1/1/2019
4	1092	1/2/2019	1/2/2019
5	1092	1/1/2019
6	1091	1/1/2019
7	1091	1/4/2019	1/4/2019	1/4/2019
8	1091	1/1/2019
Sheet457

Cell Formulas
Range	Formula
C1	=IF(COUNTIFS(A$1:A1,A1,B$1:B1,B1)=1,B1,"")
D1	=IF(A1<>E$2,"",IF(COUNTIFS(A$1:A1,E$2,B$1:B1,B1)=1,B1,""))