Merging two (or more) columns together to skip blanks

[MurdochQuill]

Hi all,

I'm using the below formula to merge columns together, but I'm wondering how I can skip blanks on the input data. The formula I'm using assumes that the ranges are static, however I am also looking to make it a dynamic formula which can handle blank cells, in which case it should skip & then add the next column instead.
Current:

Book1
	B	C	D	E	F
1	List 1		List 2		Output
2	Jamie		John		Jamie
3	Jacob		Julie		Jacob
4	Jesus				Jesus
5	Jane				Jane
6	James				James
7					John
8					Julie
Sheet1

Cell Formulas
Range	Formula
F2	F2	=IFERROR(INDEX($B$2:$B$6, ROWS(C1:$C$1)), IFERROR(INDEX($D$2:$D$3, ROWS(C1:$C$1)-ROWS($B$2:$B$6)), ""))
F3:F8	F3	=IFERROR(INDEX($B$2:$B$6, ROWS(C$1:$C2)), IFERROR(INDEX($D$2:$D$3, ROWS(C$1:$C2)-ROWS($B$2:$B$6)), ""))

However I'm hoping to achieve this:

Book1
	B	C	D	E	F
1	List 1		List 2		Output
2	Jamie		John		Jamie
3	Jacob		Julie		Jacob
4					James
5					Jesus
6					Jeff
7					Julius
8	James				John
9	Jesus				Julie
10	Jeff
11
12	Julius
Sheet1

Cell Formulas
Range	Formula
F2	F2	=IFERROR(INDEX($B$2:$B$8, ROWS(C1:$C$1)), IFERROR(INDEX($D$2:$D$3, ROWS(C1:$C$1)-ROWS($B$2:$B$8)), ""))
F3,F11:F12	F3	=IFERROR(INDEX($B$2:$B$8, ROWS(C$1:$C2)), IFERROR(INDEX($D$2:$D$3, ROWS(C$1:$C2)-ROWS($B$2:$B$8)), ""))

Any help would be great.

 

[Fluff]

Roughly how many cells will be involved?

 

[MurdochQuill]

Roughly how many cells will be involved?

Approx 30,000 cells in total (on this sheet).

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Waverley		Test Valley		Stockton-on-Tees		Waverley
3	Lambeth		Hackney		Hounslow		Lambeth
4	Bath		East Hertfordshire		Horsham		Bath
5	Exeter		Stafford		Newham		Exeter
6	Luton		North Tyneside		North East Lincolnshire		Luton
7	Waltham Forest		Nottingham		Windsor and Maidenhead		Waltham Forest
8	Brighton and Hove		Swindon		Leicester		Brighton and Hove
9	Wakefield				Sheffield		Wakefield
10	Three Rivers				Elmbridge		Three Rivers
11					Blaby		Haringey
12							Leeds
13							Test Valley
14							Hackney
15							East Hertfordshire
16							Stafford
17	Haringey						North Tyneside
18	Leeds						Nottingham
19							Swindon
20							Stockton-on-Tees
21							Hounslow
22							Horsham
23							Newham
24							North East Lincolnshire
25							Windsor and Maidenhead
26							Leicester
27							Sheffield
28							Elmbridge
29							Blaby
30
Main

Cell Formulas
Range	Formula
G2:G29	G2	=LET(Rngs,(A2:A20,C2:C20,E2:E20),Rws,ROWS(INDEX(Rngs,,,1)),Seq,SEQUENCE(AREAS(Rngs)*Rws,,0),Ary,INDEX(Rngs,MOD(Seq,Rws)+1,1,INT(Seq/Rws)+1),FILTER(Ary,Ary<>""))
Dynamic array formulas.

All the ranges must be the same size.

 

[MurdochQuill]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	Waverley		Test Valley		Stockton-on-Tees		Waverley
3	Lambeth		Hackney		Hounslow		Lambeth
4	Bath		East Hertfordshire		Horsham		Bath
5	Exeter		Stafford		Newham		Exeter
6	Luton		North Tyneside		North East Lincolnshire		Luton
7	Waltham Forest		Nottingham		Windsor and Maidenhead		Waltham Forest
8	Brighton and Hove		Swindon		Leicester		Brighton and Hove
9	Wakefield				Sheffield		Wakefield
10	Three Rivers				Elmbridge		Three Rivers
11					Blaby		Haringey
12							Leeds
13							Test Valley
14							Hackney
15							East Hertfordshire
16							Stafford
17	Haringey						North Tyneside
18	Leeds						Nottingham
19							Swindon
20							Stockton-on-Tees
21							Hounslow
22							Horsham
23							Newham
24							North East Lincolnshire
25							Windsor and Maidenhead
26							Leicester
27							Sheffield
28							Elmbridge
29							Blaby
30
Main

Cell Formulas
Range	Formula
G2:G29	G2	=LET(Rngs,(A2:A20,C2:C20,E2:E20),Rws,ROWS(INDEX(Rngs,,,1)),Seq,SEQUENCE(AREAS(Rngs)*Rws,,0),Ary,INDEX(Rngs,MOD(Seq,Rws)+1,1,INT(Seq/Rws)+1),FILTER(Ary,Ary<>""))
Dynamic array formulas.

All the ranges must be the same size.

I'm continually amazed by your Excel skills. Nice solution, very neat.

 

[Fluff]

You're welcome & thanks for the feedback.