# complex merging of columns

#### jeremypyle

##### Board Regular
Hi everyone.

This formula may be reasonable complex. I would greatly appreciate someone's help

In SHEET1, please place the following:

a1=jeremy
a2=5
a3=7

b1=matthew
b2=9
b3=2
b4=4

c1=isaac
c2=1
c3=4

column d contains nothing. It is empty

e1=joseph
e2=1
e3=3

Now in SHEET2, I want a formula that you can drag. In column A of sheet2, I want the names and in column B of sheet2, I want the numbers. I want the following to be in sheet2:

a1=jeremy
a2=jeremy
a3=matthew
a4=matthew
a5=matthew
a6=isaac
a7=isaac
a8=joseph
a9=joseph

b1=5
b2=7
b3=9
b4=2
b5=4
b6=1
b7=4
b8=1
b9=3

I want to be able to put the formula only into a1 of column a and then drag the formula down. I also want to only need to put the formula into b1 of the b column and drag the formula down.

I'm not sure what formula I would need to use for this.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Pull the formulas in A1:B1 down to cover all data (here I assumed no name in sheet1 would have numeric entries below it going past row 20 - adjust to suit. I imagine there may be less complex formulas, but these appear to work.
Excel Workbook
AB
1jeremy5
2jeremy7
3matthew9
4matthew2
5matthew4
6isacc1
7isacc4
8joseph1
9joseph3
Sheet2

Thanks so much for your help. This works. However, what happens if I was to do it for rows a all the way to s or t. Would the formula just get huge?

Oh, and you don't need to include the blank D column in your formula. But is there an easy way to increase this formula to do all the columns up to column M?

The first formula will grow in proportion to the number of columns and will eventually hit a limit on nested IFs (I think the limit is 64 for Excel 2007). Going to column M will add another 7 or 8 layers of nesting so should be doable in Excel 2007 or Excel 2010. Just look at the structure of the formula and it should be clear how to increase the number of columns - each new column adds another element to the nest.

Thanks so much for your help. I have gotten it working now. What you told me has been incredible =)

Replies
21
Views
454
Replies
1
Views
431
Replies
4
Views
258
Replies
4
Views
704
Replies
2
Views
174

1,203,109
Messages
6,053,566
Members
444,673
Latest member

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