Help combining columns without adding them mathematically

[Roobaloob]

Hello everyone,

I am new with excel and have been trying to help my boss with this simple problem all day:

I have an excel file with thousands of names in column A. In column B, I have thousands of addresses.


Example:


A.........|....B..........|

Name....Address line 1
...........Address line 2
...........Address line 3





How can I combine them so that it's arranged like labels, e.g.


A...............|...........B..............|

Name ..................Name2
Address line 1........Address Line 4
Address line 2........Address Line 5
Address line 3........Address Line 6



I am very new, and this simple problem is maddening. Maybe a pro could help me out? Thanks for the help in advance

 

[Marcelo Branco]

Are there, always, just three address lines for each name?

M.

 

[Roobaloob]

Hey there. Some of addresses have up to 4 lines.
They are all 2-4 lines.

 

[Marcelo Branco]

We could achieve what you need with a macro, but let's try a solution using formulas.

Assuming you have this in Sheet1

A B[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Name1
[/TD]
[TD="width: 102, bgcolor: transparent"]Address line 1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name2
[/TD]
[TD="bgcolor: transparent"]Address line 4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name3
[/TD]
[TD="bgcolor: transparent"]Address line 6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name4
[/TD]
[TD="bgcolor: transparent"]Address line 10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name5
[/TD]
[TD="bgcolor: transparent"]Address line 13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 15
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 16
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name6
[/TD]
[TD="bgcolor: transparent"]Address line 17
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 18
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name7
[/TD]
[TD="bgcolor: transparent"]Address line 19
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 20
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 21
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 22
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]End
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Type the word End at the last row (see above)

on a new sheet, say Sheet2

A B C................[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="width: 95, bgcolor: transparent"]Name1
[/TD]
[TD="width: 95, bgcolor: transparent"]Name2
[/TD]
[TD="width: 95, bgcolor: transparent"]Name3
[/TD]
[TD="width: 102, bgcolor: transparent"]Name4
[/TD]
[TD="width: 102, bgcolor: transparent"]Name5
[/TD]
[TD="width: 102, bgcolor: transparent"]Name6
[/TD]
[TD="width: 102, bgcolor: transparent"]Name7
[/TD]
[TD="width: 64, bgcolor: transparent"]End
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Address line 1
[/TD]
[TD="bgcolor: transparent"]Address line 4
[/TD]
[TD="bgcolor: transparent"]Address line 6
[/TD]
[TD="bgcolor: transparent"]Address line 10
[/TD]
[TD="bgcolor: transparent"]Address line 13
[/TD]
[TD="bgcolor: transparent"]Address line 17
[/TD]
[TD="bgcolor: transparent"]Address line 19
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Address line 2
[/TD]
[TD="bgcolor: transparent"]Address line 5
[/TD]
[TD="bgcolor: transparent"]Address line 7
[/TD]
[TD="bgcolor: transparent"]Address line 11
[/TD]
[TD="bgcolor: transparent"]Address line 14
[/TD]
[TD="bgcolor: transparent"]Address line 18
[/TD]
[TD="bgcolor: transparent"]Address line 20
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Address line 3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 8
[/TD]
[TD="bgcolor: transparent"]Address line 12
[/TD]
[TD="bgcolor: transparent"]Address line 15
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 21
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 9
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 16
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Address line 22
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


in A1 paste the first name of the list (Name1 in the example above)

in B1 put this formula
=INDEX(Sheet1!$A:$A,MATCH("*",INDEX(Sheet1!$A:$A,MATCH(A1,Sheet1!$A:$A,0)+1):Sheet1!$A$1000,0)+MATCH(A1,Sheet1!$A:$A,0))

copy (drag) this formula across till you get End as result

Formula in A2
=IF(ROWS(A$2:A2)>MATCH(B$1,Sheet1!$A:$A,0)-MATCH(A$1,Sheet1!$A:$A,0),"",INDEX(Sheet1!$B:$B,MATCH(A$1,Sheet1!$A:$A,0)+ROWS(A$2:A2)-1))

copy down till A5 and across till the last name in row 1

To get rid of the formulas, you can
Select the result range
Copy
Paste only values

Hope it's what you need.

M.

 

[Armando Montes]

You might try transposing each group:
http://www.techonthenet.com/excel/formulas/transpose.php

 

[Roobaloob]

Marcello and Armando, thank you for your help.

I took a screen cap, pasted it into word, and then censored out the names and addresses. I'm not sure how to attach it, as it's on my hard-drive, and not at a URL.

IF there is a quick upload URL or something like that, let me know, so I can link it!

The names are not separated by any blank rows, like you posted, Marcello. Instead, they typed the name in the A column, and the full address in each box in the B column. Sigh.

thanks again

 

[Roobaloob]