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 |