converting rows and columns to merge data

I need some help converting an exported address file so i can align the columns properly and perform a mail merge in Word.

The problem iI am having is Excel has in Row A the following

A
1)company name
2)address
3)company name
4)address
5)company name
6)address

how do I get ever second line moved to a column so everything lines up and I have two columns of name and address. I have several thousand records to fix up and make a mail merge document for a letter

thanks

In column B1 enter the formula =A2
Highlight B1 and B2
Double click on the fill box (the little black box on the lower right side of the cell) this will fill column B with the addresses.
It should look like this

Please Login or Register  to view this content.

Highlight column B.
Copy the column (Ctrl+C)
Right click on column B header and choose Paste Special... Choose values
Press F5, click on the Special button - choose Blanks
Press Ctrl +"-" (minus) or right click one of the highlighted cells and choose Delete
Choose entire row.

It's easier than it sounds - I'll post a macro soon

Here's the macro - assumes, like the formulas I posted that there is now header row.

Please Login or Register  to view this content.

If there is a header row (or rows) change the 1 in the i=1 line to the first row that has the company name.

THANK YOU VERY MUCH WORKED AWESOME!

One last question how do i go back and delete every other row so i can have blank cells and shift up

thanks

The macro will do this automatically and if you follow the instructions in my first post you will be deleting every other row.

this section:

Highlight column B.
Copy the column (Ctrl+C)
Right click on column B header and choose Paste Special... Choose values
Press F5, click on the Special button - choose Blanks
Press Ctrl +"-" (minus) or right click one of the highlighted cells and choose Delete
Choose entire row.

I spoke to quick i meant in column a so the data lines up as i now have the data in b1 in a2

thanks

I'm not sure I know what you mean.

If your data looks like this:

Please Login or Register  to view this content.

please continue with the steps posted below the code box in my first post.

If you have a header row, you'd have to but the formula in the first row where you want to move the address to an change the formula, For example, the first address is to go in B2, your formula there would be =A3.

From your example if i wanted to delete row 2,4,6 how would i do that since i have several thousand while keeping the new data 1,3,and 5

If you follow the direction in my first post to the end you will be deleting the alternating rows. It's after you Paste Special Values... Section

The part in red explains how to delete the rows

In column B1 enter the formula =A2
Highlight B1 and B2
Double click on the fill box (the little black box on the lower right side of the cell) this will fill column B with the addresses.

(table removed from here)

Highlight column B.
Copy the column (Ctrl+C)
Right click on column B header and choose Paste Special... Choose Values (click okay)
Press F5, click on the Special button - choose Blanks
Press Ctrl +"-" (minus) or right click one of the highlighted cells and choose Delete
Choose entire row (click okay).

If you want process automated, use the Macro.