Concatenate range if number is between two numbers

[erlendfs]

Hi,

Apologies in advance if this has been answered previously. I have tried searching the forum and google, but can't seem to find an answer to my issue.

I have two columns (E:F) with YYYYMM (ie. 202203 for this month) and Mmm-YY (ie Mar-22 for this month).

The data goes from January 2014 (201401 / Jan-14) to December 2027 (202712 / Des-27). At the end of each year, there is also a end of year row. The end of year row for 2027 is for instance 202713 in column E and EOY-27 in column F. All values in column E and F are created using formulas.

I would like to specify two YYYYMM values in two cells, and then a formula concatenates all YYYYMM and Mmm-YY values between these two values. For example, if I enter 202203 in the first cell, and 202201 in the second cell, the formula cell should evaluate to "202203-Mar-22-202202-Feb-22-202201-Jan-22", concatenating the values between the two ranges I specified.

Would this be possible using a formula? Alternatively using a macro?

Thanks for any help, and please let me know if you require any further details in solving this matter.

 

[kvsrinivasamurthy]

Data

202203​	Mar-22
202204​	Apr-22
202205​	May-22
202206​	Jun-22
202207​	Jul-22
202208​	Aug-22
202209​	Sep-22
202210​	Oct-22
202211​	Nov-22
202212​	Dec-22
202301​	Jan-23
202302​	Feb-23
202303​	Mar-23
202304​	Apr-23
202305​	May-23
202306​	Jun-23
202307​	Jul-23
202308​	Aug-23
202309​	Sep-23
202310​	Oct-23
202311​	Nov-23

	K	L	M
Row3	202205​	202302​	202205-May-22-202206-Jun-22-202207-Jul-22-202208-Aug-22-202209-Sep-22-202210-Oct-22-202211-Nov-22-202212-Dec-22-202301-Jan-23-202302-Feb-23

In M3 ARRAY formula. Data range E2:F22
=TEXTJOIN("-",TRUE,IF(($E$2:$E$22>=K4)*($E$2:$E$22<=L4),$E$2:$F$22,""))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[erlendfs]

Hi,

Thank you very much for your solution, kvsrinivasamurthy!

​

I have tested the formula, and I have two follow up questions:

• I can get it to work when I hardcode the Data-table. However, I would not get it to work if the Data-table is made up of formulas (which it is, and ideally it should be from an end user standpoint)
• At the end of every year, there is a EOY row, like this

202202	Feb-22
202201	Jan-22
202113	EOY-21
202112	Dec-21
202111	Nov-21
202110	Oct-21
...	...

Is there any way to factor in the EOY-row? Currently it is not added to the cell where TEXTJOIN is applied.

 

[kvsrinivasamurthy]

What are the formulas.
What is the expected answer.

 

[erlendfs]

A	B	C	D	E
EOY-	13	2022	=C2&B$2 (evaluates to 202213)	=$A$2&RIGHT(C2,2) (Evaluates to EOY-22)
Dec-	12	2022	=C3&B$3	=$A$3&RIGHT(C3,2)

As mentioned, if I copy-paste and convert to values, it works, but does not work on the EOY. Therefore, I would like for it to also capture the EOY rows, in addition to accepting formulas in D and E (where the formula you originally provided is looking).

Thank you for your help, I feel stuck...

 

[kvsrinivasamurthy]

Change this

=C2&B$2 (evaluates to 202213)

as

=Value(C2&B$2)

Similarly this

=C3&B$3

as

=Value(C3&B$3)

 

[erlendfs]

Thank you for your input. The values in column D are added now, but the Dec-22 / EOY-22 values are not added to the textjoin formula cell. Do you know how to fix this please?

 

[erlendfs]

My bad, I just retested and it seems to be working perfectly now. Thank you very much for your help!

 

[kvsrinivasamurthy]

Is possible to upload file