Help needed to do a loop/array to create a new table

[oliviameng]

Hi,

I'm writting a loop to create a new table as following and got stacked. 

Here is an example table with a table of scores for different months. There are date_1 and Date_2(table #1).

I need to create a new table(table #2) to only keep the scores from date_1 to date_2, and in line with date_2(as Mon_0).

Is there a way to write a do loop/array to solve the problem? Really appreciate.

Table #1

ID	date_1	date_2	sc_201501	sc_201502	sc_201503	sc_201504	sc_201505	sc_201506	sc_201507	sc_201508	sc_201509	sc_201510	sc_201511	sc_201512	sc_201601	sc_201602	sc_201603	sc_201604	sc_201605	sc_201606	sc_201607	sc_201608	sc_201609	sc_201610	sc_201611	sc_201612
1	2015_01	2016_02	0.658	0.888	0.330	0.938	0.148	0.062	0.598	0.944	0.770	0.189	0.144	0.842	0.372	0.481	0.123	0.630	0.276	0.292	0.626	0.665	0.885	0.614	0.331	0.467
2	2016_01	2016_12	0.414	0.915	0.748	0.160	0.388	0.100	0.751	0.089	0.786	0.126	0.305	0.852	0.874	0.875	0.414	0.404	0.268	0.730	0.760	0.443	0.577	0.231	0.641	0.398
3	2016_04	2016_12	0.614	0.166	0.465	0.347	0.680	0.042	0.400	0.959	0.223	0.603	0.918	0.954	0.289	0.480	0.626	0.638	0.451	0.478	0.197	0.175	0.093	0.096	0.535	0.990
4	2016_03	2016_11	0.054	0.871	0.087	0.286	0.801	0.701	0.617	0.015	0.215	0.546	0.467	0.219	0.561	0.397	0.878	0.481	0.418	0.887	0.758	0.575	0.750	0.364	0.898	0.437
5	2015_12	2016_05	0.888	0.453	0.649	0.617	0.734	0.244	0.512	0.033	0.964	0.416	0.739	0.183	0.665	0.588	0.962	0.755	0.500	0.236	0.983	0.843	0.256	0.489	0.394	0.682
6	2015_10	2016_03	0.517	0.493	0.239	0.960	0.050	0.082	0.701	0.576	0.892	0.446	0.167	0.467	0.085	0.047	0.492	0.837	0.289	0.107	0.840	0.442	0.951	0.785	0.169	0.173
7	2015_02	2015_12	0.206	0.587	0.518	0.233	0.906	0.983	0.273	0.845	0.600	0.514	0.920	0.182	0.433	0.026	0.948	0.281	0.347	0.272	0.818	0.054	0.415	0.759	0.547	0.319

Table #2

ID	date_1	date_2	Mon_before14	Mon_before13	Mon_before12	Mon_before11	Mon_before10	Mon_before9	Mon_before8	Mon_before7	Mon_before6	Mon_before5	Mon_before4	Mon_before3	Mon_before2	Mon_before1	Mon_0
1	2015_01	2016_02		0.658	0.888	0.330	0.938	0.148	0.062	0.598	0.944	0.770	0.189	0.144	0.842	0.372	0.481
2	2016_01	2016_12				0.874	0.875	0.414	0.404	0.268	0.730	0.760	0.443	0.577	0.231	0.641	0.398
3	2016_04	2016_12							0.638	0.451	0.478	0.197	0.175	0.093	0.096	0.535	0.990
4	2016_03	2016_11							0.878	0.481	0.418	0.887	0.758	0.575	0.750	0.364	0.898
5	2015_12	2016_05										0.183	0.665	0.588	0.962	0.755	0.500
6	2015_10	2016_03										0.446	0.167	0.467	0.085	0.047	0.492
7	2015_02	2015_12					0.587	0.518	0.233	0.906	0.983	0.273	0.845	0.600	0.514	0.920	0.182

Many thanks,

Olivia
 

[Kurt_Bremser · Accepted solution]

Transpose to a long format, then set the "month before" variable with intck(), or delete if it's before date_1 or after date_2.

If you still need the horizontal format in the output, transpose back.

[oliviameng]

Thank you. This is much easier way than writing a loop and with arrays.

Olivia