Create Adjacent Lists from One Master List

[dwgnome]

Looking to create individual lists adjacent to each other from one master list based on year.

The master list contains the names of all worksheets in the workbook as they appear in order from left to right. As you can see from the example shown, most of the names represent the month and year and there a few non-date type names.

I need to create separate lists based on the year based on the existing header names Months13, Months14, Months15, so that all the worksheet names having the number ‘13’ in them will be copied under Months13 (Jan 2013, Feb 2013, to Dec 2013). The same for the other years. The lists must be text not actual dates (numeric). These lists will then be used as dynamic <st1lace w:st="on"><st1lacename w:st="on">Named</st1lacename> <st1lacetype w:st="on">Ranges</st1lacetype></st1lace> in subsequent calculations.

I prefer a formula approach keeping in mind that the lists need to also be in chronological order as shown. Excel 2003 version.


Months

*	B	C	D	E	F
2	Months13	Months14	Months15	*	Master List
3	Jan 2013	Jan 2014	Jan 2015	*	Summary
4	Feb 2013	Feb 2014	*	*	Jan 2015
5	Mar 2013	Mar 2014	*	*	Dec 2014
6	Apr 2013	Apr 2014	*	*	Nov 2014
7	May 2013	May 2014	*	*	Oct 2014
8	Jun 2013	Jun 2014	*	*	Sep 2014
9	Jul 2013	Jul 2014	*	*	Aug 2014
10	Aug 2013	Aug 2014	*	*	Jul 2014
11	Sep 2013	Sep 2014	*	*	Jun 2014
12	Oct 2013	Oct 2014	*	*	May 2014
13	Nov 2013	Nov 2014	*	*	Apr 2014
14	Dec 2013	Dec 2014	*	*	Mar 2014
15	*	*	*	*	Feb 2014
16	*	*	*	*	Jan 2014
17	*	*	*	*	Dec 2013
18	*	*	*	*	Nov 2013
19	*	*	*	*	Oct 2013
20	*	*	*	*	Etc…
21	*	*	*	*	Jan 2013
22	*	*	*	*	Test Sheet

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Weazel]

maybe something like

G2=IFERROR(TEXT(INDEX($E$3:$E$18,SMALL(IF(RIGHT(G$1,2)=TEXT($E$3:$E$18,"yy"),ROW(E$3:E$18)-ROW(E$3)+1),ROWS(G$2:G2))),"mmm yyy"),"")

	A	B	C	D	E	F	G	H	I
1	Months13</SPAN>	Months14</SPAN>	Months15</SPAN>	*</SPAN>	Master List</SPAN>		Months13</SPAN>	Months14</SPAN>	Months15</SPAN>
2	Jan-13</SPAN>	Jan-14</SPAN>	Jan-15</SPAN>	*</SPAN>	Summary</SPAN>		Dec 2013</SPAN>	Dec 2014</SPAN>	Jan 2015</SPAN>
3	Feb-13</SPAN>	Feb-14</SPAN>	*</SPAN>	*</SPAN>	Jan-15</SPAN>		Nov 2013</SPAN>	Nov 2014</SPAN>
4	Mar-13</SPAN>	Mar-14</SPAN>	*</SPAN>	*</SPAN>	Dec-14</SPAN>		Oct 2013</SPAN>	Oct 2014</SPAN>
5	Apr-13</SPAN>	Apr-14</SPAN>	*</SPAN>	*</SPAN>	Nov-14</SPAN>			Sep 2014</SPAN>
6	May-13</SPAN>	May-14</SPAN>	*</SPAN>	*</SPAN>	Oct-14</SPAN>			Aug 2014</SPAN>
7	Jun-13</SPAN>	Jun-14</SPAN>	*</SPAN>	*</SPAN>	Sep-14</SPAN>			Jul 2014</SPAN>
8	Jul-13</SPAN>	Jul-14</SPAN>	*</SPAN>	*</SPAN>	Aug-14</SPAN>			Jun 2014</SPAN>
9	Aug-13</SPAN>	Aug-14</SPAN>	*</SPAN>	*</SPAN>	Jul-14</SPAN>			May 2014</SPAN>
10	Sep-13</SPAN>	Sep-14</SPAN>	*</SPAN>	*</SPAN>	Jun-14</SPAN>			Apr 2014</SPAN>
11	Oct-13</SPAN>	Oct-14</SPAN>	*</SPAN>	*</SPAN>	May-14</SPAN>			Mar 2014</SPAN>
12	Nov-13</SPAN>	Nov-14</SPAN>	*</SPAN>	*</SPAN>	Apr-14</SPAN>			Feb 2014</SPAN>
13	Dec-13</SPAN>	Dec-14</SPAN>	*</SPAN>	*</SPAN>	Mar-14</SPAN>			Jan 2014</SPAN>
14	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Feb-14</SPAN>
15	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Jan-14</SPAN>
16	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Dec-13</SPAN>
17	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Nov-13</SPAN>
18	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Oct-13</SPAN>
19	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Etc…</SPAN>
20	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Jan-13</SPAN>
21	*</SPAN>	*</SPAN>	*</SPAN>	*</SPAN>	Test Sheet</SPAN>

<TBODY>
</TBODY>

 

[dwgnome]

Thank you Weazel, that appears to work. Just needed to adapt the formula so that the worksheets appear in Columns A, B and C not G, H, and I. Is there any way to have the lists reversed so that Jan is at top and Dec at bottom?

 

[Weazel]

you can try replacing small with large

Excel 2012

	A	B	C	D	E	F
1	Months13	Months14	Months15			Master List
2	Feb 2013	Jan 2014	Jan 2015			Summary
3	Apr 2013	Feb 2014				Jan-15
4	Nov 2013	Mar 2014				Dec-14
5	Dec 2013	Apr 2014				Nov-14
6		May 2014				Oct-14
7		Jun 2014				Sep-14
8		Jul 2014				Aug-14
9		Aug 2014				Jul-14
10		Sep 2014				Jun-14
11		Oct 2014				May-14
12		Nov 2014				Apr-14
13		Dec 2014				Mar-14
14						Feb-14
15						Jan-14
16						Dec-13
17						Nov-13
18						Apr-13
19						Feb-13

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula A2 {=IFERROR(TEXT(INDEX($F$3:$F$19,LARGE(IF(RIGHT(A$1,2)=TEXT($F$3:$F$19,"yy"),ROW(F$3:F$19)-ROW(F$3)+1),ROWS(A$2:A2))),"mmm yyy"),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[dwgnome]

Yes, that did the trick. Many thanks for your assistance.