Add flag after a 1 but before 2

[phil133]

Hi. I have a column with dates, a column with a smaller set of dates and a column with empty cells some 1s and 2s. 1 always comes before 2. Column D has the desired result. So when it sees a 1 then if the date is also in column B (events) it should add a 3. When it sees a 2 and until it sees a 1 again, even if the date is also in column B it should not bring a 3. I'm copying the mini-sheet. Thank you.

sample.xlsx
	A	B	C	D
1	Date	Events	Triggers	Desired
2	1/1/2010	1/4/2010
3	1/4/2010	1/5/2020
4	1/5/2010	1/8/2010	1	1
5	1/6/2010	1/12/2020
6	1/7/2010	1/14/2010
7	1/8/2010	1/19/2010		3
8	1/11/2010	1/21/2020
9	1/12/2010	1/26/2010	2	2
10	1/13/2010
11	1/14/2010
12	1/15/2010
13	1/18/2010		1	1
14	1/19/2010			3
15	1/20/2010
16	1/21/2010			3
17	1/22/2010
18	1/25/2010		2	2
19	1/26/2010
20	1/27/2010
Sheet1

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B	C	D
1	Date	Events	Triggers	Desired
2	01/01/2010	04/01/2010
3	04/01/2010	05/01/2020
4	05/01/2010	08/01/2010	1	1
5	06/01/2010	12/01/2020
6	07/01/2010	14/01/2010
7	08/01/2010	19/01/2010		3
8	11/01/2010	21/01/2020
9	12/01/2010	26/01/2010	2	2
10	13/01/2010
11	14/01/2010
12	15/01/2010
13	18/01/2010		1	1
14	19/01/2010			3
15	20/01/2010
16	21/01/2010
17	22/01/2010
18	25/01/2010		2	2
19	26/01/2010
20	27/01/2010
21
Main

Cell Formulas
Range	Formula
D2:D20	D2	=IF(OR(C2={1,2}),C2,IF(AND(ISNUMBER(MATCH(A2,$B$2:$B$9,0)),LOOKUP(2,1/(C$1:C1<>""),C$1:C1)=1),3,""))

 

[phil133]

Fluff thank you. Can you please revise it a little so that if there was a 3 and 2 has not arrived yet to have another 3 if column B contains the corresponding date? For example, D16 should be 3. Thank you again. I added in my profile that I have windows and office 2016.

 

[Fluff]

A16 does not exist in col B
Thanks for updating your profile.

 

[phil133]

A16 does not exist in col B
Thanks for updating your profile.

Oops! Sorry. Thank you! I have another question but is not related to this so I will open a new thread.

 

[Fluff]

You're welcome & thanks for the feedback.