How to shift address info. from rows to columns?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you
 
Mark

First thing. Is the data consistent? You show 5 rows of data for each group.
Is this what you have in the column? If you have spaces(blank rows) between
your data as you state you should get rid of them. Select the range of data
in Column A and Edit>Go To>Special>Blanks. With blanks selected
Edit>Delete>Entire Row.

Now, 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 5. OK and your data will be laid out as shown below.

name address city state zip
name address city state zip
name address city state zip

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
 
I answered a very similar question a few weeks ago with a
full code listing ...suggest you do a search


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have a mailing list with name, address, city, state &
Click to expand...
zip with each item in individual rows like a list of
labels and a few empty rows of space between each
listing. How do I create/transfer this list into columns
accross so I can sort by city or zip? Thank you!
 
This procedure assumes
(1) addresses start at row 1
(2) addresses are three lines
(3) there is one blank row between then lines

Row 1 : Address1 line 1
Row 2 : Address1 line 2
Row 3 : Address1 line 3
Row 4 : Blank
Row 5 : Address2 line 1
Row 6 : Address2 line 2
Row 7 : Address2 line 3
Row 8 : Blank
etc

Sub AdjustAddresses()
Dim rw As Long

rw = 1

Do Until Cells(rw, 1).Value = ""

Cells(rw, 2).Value = Cells(rw + 1, 1).Value
Cells(rw, 3).Value = Cells(rw + 2, 1).Value

Rows(rw + 2).Delete
Rows(rw + 1).Delete
Rows(rw + 1).Delete

rw = rw + 1


Loop



End Sub

HTH
Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have a mailing list with name, address, city, state &
Click to expand...
zip with each item in individual rows like a list of
labels and a few empty rows of space between each
listing. How do I create/transfer this list into columns
accross so I can sort by city or zip? Thank you!
 
Please do not multi-post.

See one reply in .misc
(A possible non-macro way)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------
Mark said:
I have a mailing list with name, address, city, state & zip with each item
Click to expand...
in individual rows like a list of labels and a few empty rows of space
between each listing. How do I create/transfer this list into columns
accross so I can sort by city or zip? Thank you!
 
Back
Top