# COMBINE COLUMNS

#### LLAVIGNE

##### New Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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),"")

Replies
3
Views
206
Replies
3
Views
222
Replies
1
Views
35
Replies
1
Views
92
Replies
10
Views
154

1,203,483
Messages
6,055,672
Members
444,806
Latest member
tofanexcel

### 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.

### Which adblocker are you using?

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

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