Derive column value based on multiple criteria

[adityatandel]

I have the following data in excel. I would like to derive the "Action" column for a "Person ID" based on the Y or N indicators in the "Value" column and what's in the "Month" column

Book5
	A	B	C	D
1	Person ID	Value	Month	Action
2	1	N	1/1/23	1st action
3	2	Y	1/1/23
4	1	N	2/1/23	2nd action
5	2	N	2/1/23	1st action
6	2	Y	3/1/23
7	3	N	3/1/23	1st action
8	2	N	4/1/23	1st action
9	3	Y	4/1/23
10	4	Y	4/1/23
Sheet1

For a Person ID, if the value is Y then action should be empty
If for a Person ID, the value is N then:

1. Check the last record for that person ID and whether the month is one prior to the current month
   
   1. If a record exists for the person ID in the prior month, then check the value for that record
      
      • If the value is N then action column should be populated with "2nd action"
      • If the value is Y then action should be "1st action"
   2. If the person ID is not present in the prior month then action should be "1st action"

Once a person ID is flagged for "2nd action" that person's record will not be included in the next month

I have given some examples below:

1. For Person ID 1
   
   1. In 1/1/23 since the value is N action is "1st action"
   2. In 2/1/23 value was N and in the prior month (1/1/23) the value was also N, action is "2nd action"
2. For Person ID 2
   
   1. In 1/1/23 since value is Y action is empty
   2. In 2/1/23 since value is N and value in the prior month is Y, action is "1st action"
   3. In 3/1/23 since value is Y action is empty
   4. In 4/1/23 since value is N and value in 3/1/23 is Y then "1st action"
3. For Person ID 3
   
   1. In 3/1/23 since value is N and there is no record for this ID in the prior month (2/1/23), action is "1st action"
   2. In 4/1/23 since value is Y action is empty

Date format is mm/dd/yy

 

[Legacy 143009]

=IF(B2="N",SUMPRODUCT(--($A$2:A2=A2)*--($B$2:B2="N")*--(YEAR($C$2:C2)=YEAR(C2)*--(MONTH($C$2:C2)=MONTH(C2)))&". action","")

 

[adityatandel]

Thank you but this solution worked for me.

=IF(B2="Y","",CHOOSE(SUMPRODUCT((A$2:A2=A2)*($B$2:B2="N")*(DATEDIF($C$2:C2,C2,"YM")<=1)),"1st ","2nd ","3rd ","4th ")&"action")
Paste this in E2 and copied down