Join months name based on consecutive range and separate as well in specified format

[hananak]

Dear All,

I need your help!

I have column A in which I have months and column B which has status, it will have "Y" or will be blank. I want a formula in D6, where based on "Y" in column B, it combines the month name.
1) If the range is consecutive then the formula should give result as the first month of the consecutive range and the last month and hyphen in between (Apr - Jun) and if it is not consecutive then just the month name separated by a comma (Nov). Please see the picture to better understand the output I would like to have.

I am using Office 365.

Please see the attached picture.

 

[Peter_SSs]

Is this what you mean?

24 12 30.xlsm
	A	B	C	D
1
2	Apr	Y
3	May	Y
4	Jun	Y
5	Jul
6	Aug			Apr-Jun, Nov, Jan-Mar
7	Sep
8	Oct
9	Nov	Y
10	Dec
11	Jan	Y
12	Feb	Y
13	Mar	Y
14
Months

Cell Formulas
Range	Formula
D6	D6	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B2:B13="","x",IF(B2:B13="Y",IF(B1:B12<>"Y",A2:A13,IF(B3:B14<>"Y",A2:A13," "))))))," ","-"),"x"," "))," ",", ")

 

[hananak]

Is this what you mean?

24 12 30.xlsm
	A	B	C	D
1
2	Apr	Y
3	May	Y
4	Jun	Y
5	Jul
6	Aug			Apr-Jun, Nov, Jan-Mar
7	Sep
8	Oct
9	Nov	Y
10	Dec
11	Jan	Y
12	Feb	Y
13	Mar	Y
14
Months

Cell Formulas
Range	Formula
D6	D6	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B2:B13="","x",IF(B2:B13="Y",IF(B1:B12<>"Y",A2:A13,IF(B3:B14<>"Y",A2:A13," "))))))," ","-"),"x"," "))," ",", ")

Hi Peter,

Thanks! the formula is working but a slight adjustment is needed. Please see the attached picture. When the consecutive list has only two values, then it should be (Sep - Oct), currently it is showing SepOct.
Your help in sorting out this will be much appreciated.

 

[Peter_SSs]

When the consecutive list has only two values, then it should be (Sep - Oct),

Oops, yes I missed that circumstance. See if this fixes it without ruining another combination.

24 12 30.xlsm
	A	B	C	D
1
2	Apr	Y
3	May	Y
4	Jun	Y
5	Jul
6	Aug			Apr-Jun, Sep-Oct, Dec-Jan, Mar
7	Sep	Y
8	Oct	Y
9	Nov
10	Dec	Y
11	Jan	Y
12	Feb
13	Mar	Y
Months (2)

Cell Formulas
Range	Formula
D6	D6	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B2:B13="","x",IF(B2:B13="Y",IF(B1:B12<>"Y",A2:A13,IF(B3:B14<>"Y"," "&A2:A13," "))))))," ","-"),"x"," "))," ",", ")

 

[hananak]

Oops, yes I missed that circumstance. See if this fixes it without ruining another combination.

24 12 30.xlsm
	A	B	C	D
1
2	Apr	Y
3	May	Y
4	Jun	Y
5	Jul
6	Aug			Apr-Jun, Sep-Oct, Dec-Jan, Mar
7	Sep	Y
8	Oct	Y
9	Nov
10	Dec	Y
11	Jan	Y
12	Feb
13	Mar	Y
Months (2)

Cell Formulas
Range	Formula
D6	D6	=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B2:B13="","x",IF(B2:B13="Y",IF(B1:B12<>"Y",A2:A13,IF(B3:B14<>"Y"," "&A2:A13," "))))))," ","-"),"x"," "))," ",", ")

Hi Peter, the formula is now working. There is one thing, that I am trying to fix but it seems like it's not that simple. Currently the formula outputs Apr-Jun, I would like to change it to Apr - Jun. So basically a space before the hyphen and a space after the hyphen --> Dec - Jan. Can this be fixed?
Sorry, previously I overlooked this part.

Your help would be appreciated.

 

[Peter_SSs]

There is one thing, that I am trying to fix but it seems like it's not that simple. Currently the formula outputs Apr-Jun, I would like to change it to Apr - Jun.

Just add one extra SUBSTITUTE
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B2:B13="","x",IF(B2:B13="Y",IF(B1:B12<>"Y",A2:A13,IF(B3:B14<>"Y"," "&A2:A13," "))))))," ","-"),"x"," "))," ",", "),"-"," - ")

 

[hananak]

Just add one extra SUBSTITUTE
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(B2:B13="","x",IF(B2:B13="Y",IF(B1:B12<>"Y",A2:A13,IF(B3:B14<>"Y"," "&A2:A13," "))))))," ","-"),"x"," "))," ",", "),"-"," - ")

Thank you very much Peter for your quick response and resolving my query. The formula works perfectly, as I wanted. An interesting function, I will learn about this.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.