complex merging of columns

[jeremypyle]

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.

 

[JoeMo]

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
	A	B
1	jeremy	5
2	jeremy	7
3	matthew	9
4	matthew	2
5	matthew	4
6	isacc	1
7	isacc	4
8	joseph	1
9	joseph	3
Sheet2

 

[jeremypyle]

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?

 

[jeremypyle]

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?

 

[JoeMo]

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.

 

[jeremypyle]

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