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

T

TheTassieBFG

New Member
Joined
Jun 2, 2014
Messages
17
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
AHAIAJAKALAMAN
List1 AH3-AJ603List2 Al3-An603
DateEventCodeDateEventCode
LifeTime LunchLtLSomething@4S@4
LifeTime LunchLtLSomething@4S@4
LifeTime LunchLtLCorps Cadets(1)CC
Salvos ConnectSCCorps Cadets(1)CC
Salvos ConnectSCCorps Cadets(3)CC
Salvos ConnectSCCorps Cadets(3)CC
AM MeetingMLeadership Team MeetingLT
AM MeetingMLeadership Team MeetingLT
AM MeetingMServing Central CoastS@4
AM MeetingM
AM MeetingM
AM MeetingM

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Fri 07 Feb 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 26 Jan 14[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Fri 14 Feb 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 23 Feb 14[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Fri 21 Feb 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 02 Mar 14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Mon 10 Feb 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 06 Apr 14[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Mon 17 Feb 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 16 Feb 14[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Mon 24 Feb 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 16 Mar 14[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Sun 05 Jan 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Mon 06 Jan 14[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Sun 12 Jan 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Mon 03 Feb 14[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Sun 19 Jan 14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]Sun 30 Mar 14[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Sun 26 Jan 14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]Sun 02 Feb 14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]Sun 09 Feb 14[/TD]

[TD="align: right"][/TD]

</tbody>
Lifetime (2)​

[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"]AH3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AI3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AJ3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AL3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AM3[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AN3[/TH]
[TD="align: left"]{=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))),"")}[/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]




What I am wanting to end up with is
Excel 2010
ABC
AM MeetingM
Leadership Team MeetingLT
AM MeetingM
AM MeetingM
AM MeetingM
Something@4S@4
AM MeetingM
Leadership Team MeetingLT
LifeTime LunchLtL
AM MeetingM
Salvos ConnectSC
LifeTime LunchLtL
Corps Cadets(3)CC
Salvos ConnectSC
LifeTime LunchLtL
Something@4S@4
Salvos ConnectSC
Corps Cadets(1)CC
Corps Cadets(3)CC
Serving Central CoastS@4
Corps Cadets(1)CC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Sun 05 Jan 14[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Mon 06 Jan 14[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Sun 12 Jan 14[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Sun 19 Jan 14[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]Sun 26 Jan 14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Sun 26 Jan 14[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Sun 02 Feb 14[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Mon 03 Feb 14[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Fri 07 Feb 14[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]Sun 09 Feb 14[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]Mon 10 Feb 14[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]Fri 14 Feb 14[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]Sun 16 Feb 14[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]Mon 17 Feb 14[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]Fri 21 Feb 14[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]Sun 23 Feb 14[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]Mon 24 Feb 14[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]Sun 02 Mar 14[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]Sun 16 Mar 14[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]Sun 30 Mar 14[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]Sun 06 Apr 14[/TD]

</tbody>
Lifetime (2)​




your help would be greatly appreciated
Andrew
 
You must log in or register to reply here.

Similar threads

E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
344
Peter_SSs
Peter_SSs
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
604
Eric W
Eric W
E
  • Solved
Formula not working
Replies
2
Views
376
esmarques
E
K
  • Question Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
1K
arthurbr
arthurbr
E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
411
Fluff
Fluff

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