Can this be done?

[BigBuck98]

I have a file that has a list of 750 names and addresses that are on rows
rather than in columns as the example below shows.

Joe Blow
123 Anywhere Street
Anytown, ST #####

I need to put these names and addressed in columns, they all have a blank
row between them.

Joe Blow 123 Anywhere Street Anytown, ST #####

So that I can do a mail merge to print mailing labels. Is there any formula
that I can use to get these names into columns or will I need to type each
one into a new file?
Any help will be greatly appreciated.

Gordon

 

[drabbacs]

There are probably quicker ways, but here is a solution.
This assumes each address is 3 rows followed by a blank
4th row.

First you need a couple helper columns. Insert a column to
the left of the addresses and number them 1,2,3 etc. Let's
say that's column A and your addresses are in B. Then in C
do number 1 to 750(or however many addresses you have)

Assuming the first entry is in row2:

in D
=vlookup((c2+0)+(c2-1)*3,$A:$A,2,"true")

in E
=vlookup((c2+1)+(c2-1)*3,$A:$A,2,"true")

in F
=vlookup((c2+2)+(c2-1)*3,$A:$A,2,"true")

Then copy D2,E2, and F2 down to the end.


Good Luck

 

[Mae]

I'm not sure if you mean that you just have a single
column, with hundreds of rows; or if you have a row of all
the names, a row of all addresses, and a row of all the
citystatezips. If it's just the three rows with hundreds
of columns, just select all of your data, copy it, right
click and choose Paste Special, then click the Transpose
button.

If it's the first way, one idea is to write a macro that
will transpose each address individually. I'm not that
great with VB, but you could probably record most of it --
Select and copy the first set of info, transpose it onto a
new sheet, then go back and delete those rows so that the
next address is right at the top -- then edit the macro to
repeat 749 times, making sure to go down to the next blank
row when you paste it onto the new sheet.

That's one idea, but make sure you have a backup first!!
Try this out on a copy so if it's not what you were trying
to get, you don't lose all your data.

-- Mae

 

[Gord Dibben]

Gordon

Yes, it can be done.

One method involves a macro...

First thing. Is the data consistent? You state 3 rows of data for each group
and a blank row. Is this what you have in the column?

Copy/paste this macro to a Module in your workbook. To get to the Visual
Basic Editor hit ALT + F11. Select from Menu Insert>Module and paste in the
code.

ALT + Q to go back to your worksheet. ALT + F8 to open Macro dialog box.
Select the coltorows macro and Run it.

You will be asked how many columns you wish. In the case of your example you
would pick 4. OK and your data will be laid out as shown below.

A1 A2 A3 A4(which is blank)
A5 A6 A7 A8(blank again)

If your data is not consistent, maybe an address or a state missing, you
could leave a blank space in that row of your list. Try it and see if it
meets your needs.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
If Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub

Gord Dibben XL2002

 

[BigBuck98]

Thanks Gord the Macro worked great!

Gordon

Gordon

Yes, it can be done.

One method involves a macro...

First thing. Is the data consistent? You state 3 rows of data for each group
and a blank row. Is this what you have in the column?

Copy/paste this macro to a Module in your workbook. To get to the Visual
Basic Editor hit ALT + F11. Select from Menu Insert>Module and paste in the
code.

ALT + Q to go back to your worksheet. ALT + F8 to open Macro dialog box.
Select the coltorows macro and Run it.

You will be asked how many columns you wish. In the case of your example you
would pick 4. OK and your data will be laid out as shown below.

A1 A2 A3 A4(which is blank)
A5 A6 A7 A8(blank again)

If your data is not consistent, maybe an address or a state missing, you
could leave a blank space in that row of your list. Try it and see if it
meets your needs.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
If Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub

Gord Dibben XL2002

 

[Gord Dibben]

Thanks for the feedback. Always appreciated.

Gord

 

[BigBuck98]

Drabbacs,
Thanks for replying to my post. I was able to accomplish what I wanted to do
with the Macro that Gord wrote for me, however I am still playing with the
suggestion that you gave me and I cannot get it to work. I keep getting a
#REF! error message. Maybe I do not understand what you are telling me to
do. Could you look at the following url where I have saved what I was trying
and see if I have everything as you instructed me to do.
http://playmakerpa.tripod.com/excel/HanCC.htm
Thanks for your help.
Gordon

 

[Clyde Lomax]

Thanks Gord I have a copy as well.

lomax

Thanks for the feedback. Always appreciated.

Gord

Thanks Gord the Macro worked great!

Gordon

Gordon

Yes, it can be done.

One method involves a macro...

First thing. Is the data consistent? You state 3 rows of data for

each
group

and a blank row. Is this what you have in the column?

Copy/paste this macro to a Module in your workbook. To get to the Visual
Basic Editor hit ALT + F11. Select from Menu Insert>Module and paste

in
the

code.

ALT + Q to go back to your worksheet. ALT + F8 to open Macro dialog box.
Select the coltorows macro and Run it.

You will be asked how many columns you wish. In the case of your

example
you

would pick 4. OK and your data will be laid out as shown below.

A1 A2 A3 A4(which is blank)
A5 A6 A7 A8(blank again)

If your data is not consistent, maybe an address or a state missing, you
could leave a blank space in that row of your list. Try it and see if it
meets your needs.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
If Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
End Sub

Gord Dibben XL2002