Formula for assigning value (before, during, or after) based on unique events by row?

[TorontoRaps]

Hi all,

I'm sorry the title of this post isn't clear, but I'm having a hard time articulating this (hopefully my screenshot will help).

I'm really struggling to come up with a formula to decipher when a unique event (column c) happens to a unique ID (column a), and then generate a label whether that event is before, during, or after the unique event (in column d). I'm hoping what I wrote out in column d could be automatically generated using this formula.

So far I've just been doing this manually but there are thousands of rows.

Thank you in advance for any help I can get.

 

[Ezguy4u]

Toronto, at a minimum using XL2BB might get other members to help. So now the questions. Are the 2 data sheets what you want? In other words, the input data sheet is only missing before, during or after, while ID, Date and Unique Event are filled in. And how about the date. Is the during date always going to be 06/12/2021? And if there is one ID then that will be during? Let's get the ball rollin, we have to start some place.

22-11-20 work 1.xlsx
	A	B	C	D
1	ID	Date	Unique Event	Event
2	1	03/04/2021	NO
3	1	06/12/2021	YES
4	1	09/04/2021	NO
5	1	12/12/2021	NO
6	2	03/04/2021	NO
7	2	06/12/2021	YES
8	2	09/04/2021	NO
9	3	12/12/2021	YES
10	4	03/04/2021	YES
11	4	06/12/2021	NO
12	4	09/04/2021	NO
13	4	12/12/2021	NO
14	5	03/04/2021	NO
15	5	06/12/2021	YES
16	5	09/04/2021	NO
17	5	12/12/2021	NO
Data input

22-11-20 work 1.xlsx
	A	B	C	D
1	ID	Date	Unique Event	Event
2	1	03/04/2021	NO	Before
3	1	06/12/2021	YES	During
4	1	09/04/2021	NO	After
5	1	12/12/2021	NO	After
6	2	03/04/2021	NO	Before
7	2	06/12/2021	YES	During
8	2	09/04/2021	NO	After
9	3	12/12/2021	YES	During
10	4	03/04/2021	YES	During
11	4	06/12/2021	NO	After
12	4	09/04/2021	NO	After
13	4	12/12/2021	NO	After
14	5	03/04/2021	NO	Before
15	5	06/12/2021	YES	During
16	5	09/04/2021	NO	After
17	5	12/12/2021	NO	After
Data output