COMBINE COLUMNS

[LLAVIGNE]

Can anyone tell me a formula that will combine two columns into one, with the second column listed on every other line of the first.
ex.
columnA columnB
12463090 1
10463651 2


end result:
12463090
1
10463651
2

 

[Aladin Akyurek]

Let A2:B4 house the sample you provided with labels X and Y in A2:B2.

In E2 enter: 2

which specifies the number of the columns you want to merge.

E2 musth house a 0.

E3, copied down:

=IF(ROW()-ROW(E$3)+1<=$F$1,LOOKUP(9.99999999999999E+307,$E$2:E2)+(MOD(COUNTA($E$2:E2)-1,$E$1)=0),"")

F1:

=MAX(COUNTA(A3:A4),COUNTA(B3:B4))*E1

Calculates the number of data points to be merged.

In F2 enter the label: Merge

F3, copied down:

=IF(N(E3),INDEX($A$3:$B$4,E3,IF((E2<>0)*(E3<>E4),2,1)),"")

G2 must house a 0.

G3, copied down:

=IF(N(F3),LOOKUP(9.99999999999999E+307,$G$2:G2)+1,"")

H1:

=LOOKUP(9.99999999999999E+307,G:G)

In H2 enter the label: Single List

H3, copied down:

=IF(ROW()-ROW(H$3)+1<=$H$1,LOOKUP(ROW()-ROW(H$3)+1,G:G,F:F),"")