Create Adjacent Lists from One Master List

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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 <st1:place w:st="on"><st1:placename w:st="on">Named</st1:placename> <st1:placetype w:st="on">Ranges</st1:placetype></st1:place> 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

*BCDEF
*
**
**
**
**
**
**
**
**
**
**
**
****
****
****
****
****
****
****
****

<tbody>
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #FFFFCC, align: center"]Months13[/TD]
[TD="bgcolor: #FFFFCC, align: center"]Months14[/TD]
[TD="bgcolor: #FFFFCC, align: center"]Months15[/TD]
[TD="bgcolor: #FFFFCC"]*[/TD]
[TD="bgcolor: #FFFFCC, align: center"]Master List[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]Jan 2013[/TD]
[TD="align: center"]Jan 2014[/TD]
[TD="align: center"]Jan 2015[/TD]

[TD="align: left"]Summary[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]Feb 2013[/TD]
[TD="align: center"]Feb 2014[/TD]

[TD="align: left"]Jan 2015[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]Mar 2013[/TD]
[TD="align: center"]Mar 2014[/TD]

[TD="align: left"]Dec 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]Apr 2013[/TD]
[TD="align: center"]Apr 2014[/TD]

[TD="align: left"]Nov 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]May 2013[/TD]
[TD="align: center"]May 2014[/TD]

[TD="align: left"]Oct 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]Jun 2013[/TD]
[TD="align: center"]Jun 2014[/TD]

[TD="align: left"]Sep 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]Jul 2013[/TD]
[TD="align: center"]Jul 2014[/TD]

[TD="align: left"]Aug 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]Aug 2013[/TD]
[TD="align: center"]Aug 2014[/TD]

[TD="align: left"]Jul 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: center"]Sep 2013[/TD]
[TD="align: center"]Sep 2014[/TD]

[TD="align: left"]Jun 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: center"]Oct 2013[/TD]
[TD="align: center"]Oct 2014[/TD]

[TD="align: left"]May 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: center"]Nov 2013[/TD]
[TD="align: center"]Nov 2014[/TD]

[TD="align: left"]Apr 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: center"]Dec 2013[/TD]
[TD="align: center"]Dec 2014[/TD]

[TD="align: left"]Mar 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="align: left"]Feb 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

[TD="align: left"]Jan 2014[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]

[TD="align: left"]Dec 2013[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]

[TD="align: left"]Nov 2013[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]

[TD="align: left"]Oct 2013[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]

[TD="align: left"]Etc…[/TD]

[TD="bgcolor: #CACACA, align: center"]21[/TD]

[TD="align: left"]Jan 2013[/TD]

[TD="bgcolor: #CACACA, align: center"]22[/TD]

[TD="align: left"]Test Sheet[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Sort by date Sort by votes
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"),"")

[TABLE="class: grid, width: 661"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Months13</SPAN>
[/TD]
[TD]Months14</SPAN>
[/TD]
[TD]Months15</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Master List</SPAN>
[/TD]
[TD][/TD]
[TD]Months13</SPAN>
[/TD]
[TD]Months14</SPAN>
[/TD]
[TD]Months15</SPAN>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Jan-13</SPAN>
[/TD]
[TD]Jan-14</SPAN>
[/TD]
[TD]Jan-15</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Summary</SPAN>
[/TD]
[TD][/TD]
[TD]Dec 2013</SPAN>
[/TD]
[TD]Dec 2014</SPAN>
[/TD]
[TD]Jan 2015</SPAN>
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Feb-13</SPAN>
[/TD]
[TD]Feb-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Jan-15</SPAN>
[/TD]
[TD][/TD]
[TD]Nov 2013</SPAN>
[/TD]
[TD]Nov 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Mar-13</SPAN>
[/TD]
[TD]Mar-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Dec-14</SPAN>
[/TD]
[TD][/TD]
[TD]Oct 2013</SPAN>
[/TD]
[TD]Oct 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Apr-13</SPAN>
[/TD]
[TD]Apr-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Nov-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]May-13</SPAN>
[/TD]
[TD]May-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Oct-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Aug 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Jun-13</SPAN>
[/TD]
[TD]Jun-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Sep-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jul 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Jul-13</SPAN>
[/TD]
[TD]Jul-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Aug-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jun 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Aug-13</SPAN>
[/TD]
[TD]Aug-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Jul-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]May 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Sep-13</SPAN>
[/TD]
[TD]Sep-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Jun-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Oct-13</SPAN>
[/TD]
[TD]Oct-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]May-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Nov-13</SPAN>
[/TD]
[TD]Nov-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Apr-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Dec-13</SPAN>
[/TD]
[TD]Dec-14</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Mar-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan 2014</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Feb-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Jan-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Dec-13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Nov-13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Oct-13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Etc…</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Jan-13</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]*</SPAN>
[/TD]
[TD]Test Sheet</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
you can try replacing small with large

Excel 2012[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Months13[/TD]
[TD="bgcolor: #FFFF00"]Months14[/TD]
[TD="bgcolor: #FFFF00"]Months15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Master List[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Feb 2013[/TD]
[TD]Jan 2014[/TD]
[TD]Jan 2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Summary[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Apr 2013[/TD]
[TD]Feb 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Jan-15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Nov 2013[/TD]
[TD]Mar 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Dec-14[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Dec 2013[/TD]
[TD]Apr 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Nov-14[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD]May 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Oct-14[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD]Jun 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Sep-14[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD]Jul 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Aug-14[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD]Aug 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Jul-14[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD]Sep 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Jun-14[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD]Oct 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]May-14[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][/TD]
[TD]Nov 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Apr-14[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD]Dec 2014[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Mar-14[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Feb-14[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Jan-14[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Dec-13[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Nov-13[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Apr-13[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Feb-13[/TD]
[/TR]
</tbody>[/TABLE]
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=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"),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Select current month in combobox on userform for English language
2 3
Replies
29
Views
648
Ali M
A
S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
163
AhoyNC
A
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
738
Joe4
Joe4
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
77
HighAndWilder
H
M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
162
MrsFraser07
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top