Formula - Based on position of one specific value within a subset

[Jqarga]

Hi guys,

Very specific query for a workbook I'm building so I hope you can help me.

I need to incorporate a formula in column D(Flag) so this field populates one of these three labels (First time, Prior, Before) when the following scenarios occur.

The logic/formula needs to be reset every time a new entity appears in column A:

• Populates "First time" when "Y" appears for the first time in the Release column
• Populates "After" to everything that happens after the first time that "Y" appears in the Release column
• Populates "Before" for everything that happens before the first time that "Y" appears in the Release column

Sample below:

Name	Period	Release	Flag
Entity 1	1​	N	Prior
Entity 1	2​	Y	First time
Entity 1	3​	Y	After
Entity 2	1​	Y	First time
Entity 2	2​	Y	After
Entity 2	3​	N	After
Entity 2	4​	N	After
Entity 2	5​	Y	After
Entity 3	1​	N	Prior
Entity 3	2​	N	Prior
Entity 3	3​	N	Prior
Entity 3	4​	Y	First Time
Entity 3	5​	N	After

Many thanks,

JG

 

[C Moore]

I started with this to find a pattern
=COUNTIFS($A$2:A2,A2,$C$2:C2,"Y")
and noticed 0 was always prior, >1 was always after, 1 was either first time or after. This long formula returns the correct flag. Assumes Name is in A1.
=IF(COUNTIFS($A$2:A2,A2,$C$2:C2,"Y")=0,"Prior",IF(COUNTIFS($A$2:A2,A2,$C$2:C2,"Y")>1,"After",IF(AND(COUNTIFS($A$2:A2,A2,$C$2:C2,"Y")=1,C2="N"),"After","First time")))

 

[Eric W]

Another option:

Book1
	A	B	C	D
1	Name	Period	Release	Flag
2	Entity 1	1	N	Prior
3	Entity 1	2	Y	First time
4	Entity 1	3	Y	After
5	Entity 2	1	Y	First time
6	Entity 2	2	Y	After
7	Entity 2	3	N	After
8	Entity 2	4	N	After
9	Entity 2	5	Y	After
10	Entity 3	1	N	Prior
11	Entity 3	2	N	Prior
12	Entity 3	3	N	Prior
13	Entity 3	4	Y	First time
14	Entity 3	5	N	After
Sheet13

Cell Formulas
Range	Formula
D2:D14	D2	=IF(COUNTIFS($A$1:$A1,A2,$C$1:$C1,"Y"),"After",IF(C2="Y","First time","Prior"))

 

[Jqarga]

Thank you both! That worked