Select # of Months Based on Yes/No in Table

[nirvehex]

Hi All,

This is a tough one that I've been doing manually:

I have a table that looks like this:

Implementation Months

# of Implementation Months

Jun - 20

Jul - 20

Aug - 20

Sep - 20

Oct - 20

Nov - 20

Dec - 20

Jan - 21

Feb - 21

Mar - 21

Apr - 21

May - 21

Oct-May

8

Yes

Yes

Yes

Yes

Yes

Yes

Apr-Oct

7

Yes

Yes

Yes

Yes

Yes

Yes

Sep-May

9

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Nov-Feb / May-Jun

6

Yes

Yes

Yes

Yes

Yes

Yes

Aug-Apr

9

Yes

Yes

Yes

Yes

Yes

Yes

Dec-Jul

8

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Jan-May

5

Yes

Yes

Yes

Yes

Sep-Jan / Apr-May

7

Yes

Yes

Yes

Yes

Yes

Yes

Nov-Apr

6

Yes

Yes

Yes

Yes

Yes

Dec-Jun

7

Yes

Yes

Yes

Yes

Yes

Yes

Yes

I'm trying to fill in "Implementation Months" and "# of Implementation Months" based on the following logic:

(1) for "Yes" streaks longer than or equal to 2 months and
(2) where "Yes" appears continuously with at most 1 blank month in between; if multiple streaks break out streaks with a " / " like "Nov-Feb / May-Jun"
(3) generate a formula for "Implementation Months" column and "# of Implementation Months" column

I have filled in the correct answers for the above table in the first two columns. Can anyone please help me with figuring out a formula so I don't have to do this manually? I am at such a loss here. Thank you!

 

[Eric W]

Quite tough indeed! This might be better suited to VBA actually, but I managed to get it to work. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Implementation Months	# of Implementation Months	20-Jun	20-Jul	20-Aug	20-Sep	20-Oct	20-Nov	20-Dec	21-Jan	21-Feb	21-Mar	21-Apr	21-May
2	Oct-May	8					Yes	Yes	Yes	Yes		Yes		Yes		5			12
3	Apr-Oct	7	Yes	Yes		Yes	Yes						Yes	Yes		11			5
4	Sep-May	9				Yes	Yes	Yes	Yes	Yes		Yes	Yes	Yes		4			12
5	Nov-Feb / May-Jun	6	Yes					Yes	Yes	Yes	Yes			Yes		6	12		9	1
6	Aug-Apr	9			Yes		Yes		Yes	Yes	Yes		Yes			3			11
7	Dec-Jul	8	Yes	Yes					Yes	Yes	Yes	Yes	Yes	Yes		7			2
8	Jan-May	5								Yes		Yes	Yes	Yes		8			12
9	Sep-Jan / Apr-May	7				Yes		Yes	Yes	Yes			Yes	Yes		4	11		8	12
10	Nov-Apr	6						Yes	Yes		Yes	Yes	Yes			6			11
11	Dec-Jun	7	Yes			Yes			Yes	Yes		Yes	Yes	Yes		4	7		4	1
Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=IFERROR(TEXTJOIN(" / ",,IF(P2#<>S2#,TEXT(INDEX(C$1:N$1,P2#),"mmm")&"-"&TEXT(INDEX(C$1:N$1,S2#),"mmm"),"")),TEXT(C$1,"mmm")&"-"&TEXT(N$1,"mmm"))
B2:B11	B2	=IFERROR(SUM(IF(S2#=P2#,0,IF(S2#>P2#,S2#-P2#,S2#+12-P2#)+1)),12)
P2:P4,P6:P8,P10,P11:Q11,P9:Q9,P5:Q5	P2	=FILTER(COLUMN(C2:N2)-COLUMN(C2)+1,IF(INDEX(C2:N2,MOD(COLUMN(C2:N2)-COLUMN(C2)-2,12)+1)="","N","Y")&IF(INDEX(C2:N2,MOD(COLUMN(C2:N2)-COLUMN(C2)-1,12)+1)="","N","Y")&IF(C2:N2="","N","Y")="NNY")
S2:S4,S6:S8,S10,S11:T11,S9:T9,S5:T5	S2	=MOD((FIND("YNN.",TEXTJOIN(".",,IF(INDEX(C2:N2,MOD(SEQUENCE(,23)-1,12)+1)="","N","Y")&IF(INDEX(C2:N2,MOD(SEQUENCE(,23,2)-1,12)+1)="","N","Y")&IF(INDEX(C2:N2,MOD(SEQUENCE(,23,3)-1,12)+1)="","N","Y")),(P2#-1)*4)-1)/4,12)+1

I needed some helper columns. And I'm not quite sure why the P2 formula didn't show up as a Dynamic Array formula, since it uses FILTER, and S2 uses SEQUENCE.

 

[Eric W]

Much shorter versions of the P and S formulas:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Implementation Months	# of Implementation Months	20-Jun	20-Jul	20-Aug	20-Sep	20-Oct	20-Nov	20-Dec	21-Jan	21-Feb	21-Mar	21-Apr	21-May
2	Oct-May	8					Yes	Yes	Yes	Yes		Yes		Yes		5			12
3	Apr-Oct	7	Yes	Yes		Yes	Yes						Yes	Yes		11			5
4	Sep-May	9				Yes	Yes	Yes	Yes	Yes		Yes	Yes	Yes		4			12
5	Nov-Feb / May-Jun	6	Yes					Yes	Yes	Yes	Yes			Yes		6	12		9	1
6	Aug-Apr	9			Yes		Yes		Yes	Yes	Yes		Yes			3			11
7	Dec-Jul	8	Yes	Yes					Yes	Yes	Yes	Yes	Yes	Yes		7			2
8	Jan-May	5								Yes		Yes	Yes	Yes		8			12
9	Sep-Jan / Apr-May	7				Yes		Yes	Yes	Yes			Yes	Yes		4	11		8	12
10	Nov-Apr	6						Yes	Yes		Yes	Yes	Yes			6			11
11	Dec-Jun	7	Yes			Yes			Yes	Yes		Yes	Yes	Yes		4	7		4	1
Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=IFERROR(TEXTJOIN(" / ",,IF(P2#<>S2#,TEXT(INDEX(C$1:N$1,P2#),"mmm")&"-"&TEXT(INDEX(C$1:N$1,S2#),"mmm"),"")),TEXT(C$1,"mmm")&"-"&TEXT(N$1,"mmm"))
B2:B11	B2	=IFERROR(SUM(IF(S2#=P2#,0,IF(S2#>P2#,S2#-P2#,S2#+12-P2#)+1)),12)
P2:P4,P6:P8,P10,P11:Q11,P9:Q9,P5:Q5	P2	=FILTER(SEQUENCE(,12),MID(CONCAT(IF(M2:N2="","N","Y"),IF(C2:N2="","N","Y")),SEQUENCE(,12),3)="NNY")
S2:S4,S6:S8,S10,S11:T11,S9:T9,S5:T5	S2	=MOD(FIND("YNN",CONCAT(IF(C2:N2="","N","Y"),IF(C2:N2="","N","Y")),P2#)-1,12)+1

 

[Eric W]

And a shorter A2 formula:

=TEXTJOIN(" / ",,IF(P2#<>S2#,TEXT(INDEX(C$1:N$1,IFERROR(P2#,1)),"mmm")&"-"&TEXT(INDEX(C$1:N$1,IFERROR(S2#,12)),"mmm"),""))

Edit: Oops! Better stick with the original. This fails with a 12-month stretch.

 

[nirvehex]

Eric W - Thank you so much! The first example you constructed worked perfectly!! Truly, thank you, this is going to save me hours and time is so valuable! Really, really appreciate it!

 

[Eric W]

Glad it works for you! That was interesting to figure out. If the original formulas are working for you, by all means keep using them. But the P2 and S2 formulas from post 3 are a lot shorter and easier to understand.

Anyway, thanks for the feedback! ?