Merge/Continuous list from two ranges, both three columns wide - using formula if possible

[TheTassieBFG]

Hi I am working on an annual planner and want to get dates to autofill in required cells (this bit works) the issue i am having is in compiling/merging the information for these lists. I have found ways of doing it for one column however i want to copy over the three columns from each range (I do not mind if rows 1 & 2 are omitted as they are not relevant to the lists) The desired result is three columns wide showing date, event, code and if possible sorted by date then code.

The two lists shown have been compiled from event calendars with some being weekly, others are monthly or on the x Sunday of the month. currently they are listed as they appear and have not been sorted into date order. (Array Formulas shown)

A solution as a formula rather than a macro would be appreciated (if what I am asking is even possible by formula)

If it is of any use I have named two ranges List1 (AH3-AJ603)& List2 (AL3-AN603) both of which contain empty rows, list 1 is the longest and goes to row 173.


Excel 2010

	AH	AI	AJ	AK	AL	AM	AN
1	List1 AH3-AJ603				List2 Al3-An603
2	Date	Event	Code		Date	Event	Code
3	Fri 07 Feb 14	LifeTime Lunch	LtL		Sun 26 Jan 14	Something@4	S@4
4	Fri 14 Feb 14	LifeTime Lunch	LtL		Sun 23 Feb 14	Something@4	S@4
5	Fri 21 Feb 14	LifeTime Lunch	LtL		Sun 02 Mar 14	Corps Cadets(1)	CC
6	Mon 10 Feb 14	Salvos Connect	SC		Sun 06 Apr 14	Corps Cadets(1)	CC
7	Mon 17 Feb 14	Salvos Connect	SC		Sun 16 Feb 14	Corps Cadets(3)	CC
8	Mon 24 Feb 14	Salvos Connect	SC		Sun 16 Mar 14	Corps Cadets(3)	CC
9	Sun 05 Jan 14	AM Meeting	M		Mon 06 Jan 14	Leadership Team Meeting	LT
10	Sun 12 Jan 14	AM Meeting	M		Mon 03 Feb 14	Leadership Team Meeting	LT
11	Sun 19 Jan 14	AM Meeting	M		Sun 30 Mar 14	Serving Central Coast	S@4
12	Sun 26 Jan 14	AM Meeting	M
13	Sun 02 Feb 14	AM Meeting	M
14	Sun 09 Feb 14	AM Meeting	M

<tbody>
</tbody>

Lifetime (2)​

Array Formulas Cell Formula AH3 {=IFERROR(INDEX(M$1:M$600,SMALL(IF(ISNUMBER(M$1:M$600)*(NOT($N$1:$N$600="")),ROW($M$1:$M$600)-ROW($M$1)+1),ROWS(AH$3:AH3))),"")} AI3 {=IFERROR(INDEX(N$1:N$600,SMALL(IF(ISNUMBER(M$1:M$600)*(NOT($N$1:$N$600="")),ROW($M$1:$M$600)-ROW($M$1)+1),ROWS(AI$3:AI3))),"")} AJ3 {=IFERROR(INDEX(O$1:O$600,SMALL(IF(ISNUMBER(M$1:M$600)*(NOT($N$1:$N$600="")),ROW($M$1:$M$600)-ROW($M$1)+1),ROWS(AJ$3:AJ3))),"")} AL3 {=IFERROR(INDEX(X$1:X$600,SMALL(IF(ISNUMBER($X$1:$X$600)*(NOT($Y$1:$Y$600="")),ROW($X$1:$X$600)-ROW($X$1)+1),ROWS(AL$3:AL3))),"")} AM3 {=IFERROR(INDEX(Y$1:Y$600,SMALL(IF(ISNUMBER($X$1:$X$600)*(NOT($Y$1:$Y$600="")),ROW($X$1:$X$600)-ROW($X$1)+1),ROWS(AM$3:AM3))),"")} AN3 {=IFERROR(INDEX(Z$1:Z$600,SMALL(IF(ISNUMBER($X$1:$X$600)*(NOT($Y$1:$Y$600="")),ROW($X$1:$X$600)-ROW($X$1)+1),ROWS(AN$3:AN3))),"")} <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>

What I am wanting to end up with is
Excel 2010

	A	B	C
1	Sun 05 Jan 14	AM Meeting	M
2	Mon 06 Jan 14	Leadership Team Meeting	LT
3	Sun 12 Jan 14	AM Meeting	M
4	Sun 19 Jan 14	AM Meeting	M
5	Sun 26 Jan 14	AM Meeting	M
6	Sun 26 Jan 14	Something@4	S@4
7	Sun 02 Feb 14	AM Meeting	M
8	Mon 03 Feb 14	Leadership Team Meeting	LT
9	Fri 07 Feb 14	LifeTime Lunch	LtL
10	Sun 09 Feb 14	AM Meeting	M
11	Mon 10 Feb 14	Salvos Connect	SC
12	Fri 14 Feb 14	LifeTime Lunch	LtL
13	Sun 16 Feb 14	Corps Cadets(3)	CC
14	Mon 17 Feb 14	Salvos Connect	SC
15	Fri 21 Feb 14	LifeTime Lunch	LtL
16	Sun 23 Feb 14	Something@4	S@4
17	Mon 24 Feb 14	Salvos Connect	SC
18	Sun 02 Mar 14	Corps Cadets(1)	CC
19	Sun 16 Mar 14	Corps Cadets(3)	CC
20	Sun 30 Mar 14	Serving Central Coast	S@4
21	Sun 06 Apr 14	Corps Cadets(1)	CC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Lifetime (2)​

your help would be greatly appreciated
Andrew