Merge 2 columns into 1 formula

E1:

=COUNTA(B2:B7)

E2:

=COUNTA(C2:C10)

E4, copied down:

=IF(ROWS($E$4:E4)<=$E$1+$E$2,IF(ROWS($E$4:E4)>$E$1,INDEX($C$2:$C$10,ROWS($E$4:E4)-$E$1),INDEX($B$2:$B$7,ROWS($E$4:E4))),"")

Great thank you so much Aladin Akyurek!


What if I have more columns I would like to merge how would we go about modifying the formula?


Example below.

G1: 0
G2:

=G1+COUNTA(A2:A7)

G3:

=G2+COUNTA(B2:B10)

G4:

=G3+COUNTA(C2:C12)

G5:

=G4+COUNTA(D2:D12)

G6:

=G5+COUNTA(E2:E12)

H2, copied down:

=IF(ROWS($H$2:H2)<=$G$6,INDEX(CHOOSE(MATCH(ROWS($H$2:H2)-(LOOKUP(ROWS($H$2:H2),$G$1:$G$6)=ROWS($H$2:H2)),$G$1:$G$6),$A$2:$A$7,$B$2:$B$10,$C$2:$C$12,$D$2:$D$12,$E$2:$E$12),ROWS($H$2:H2)-LOOKUP(ROWS($H$2:H2)-1,$G$1:$G$6)),"")

Thank very much Adladin. It works great!