?
Solved

Convert column of addresses into multiple columns for each address element

Posted on 2014-03-26
5
Medium Priority
?
235 Views
Last Modified: 2014-03-26
I've imported a list of addresses into Excel and each line is listed in individual cells in 1 column.

I'd like to easily separate this out into manageable columns.  for instance.

it lays out like.

organization
person
address
city state zip
phone
organization
person
address
city state zip
phone
...

I'd like to convert to columns so it lays out

organization   person   street   city state zip  phone
organization   person   street   city state zip  phone

I can convert so that each line is separated by comma or whatever, but can't get it to convert.  Its 415 rows so not terrible but more than I want to handle manually.

Advice?
0
Comment
Question by:axessJosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 39957480
Assuming obviously that all your data has five rows, this code will do that:

Sub TurnRowsToColumns()
Dim shtOrg As Worksheet, shtDest As Worksheet
Dim lLastRow As Long, lRowLoop As Long

'turn off updates to speed up code execution
With application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Set shtOrg = ActiveSheet
Set shtDest = Sheets.Add
 lLastRow = shtOrg.Cells(Rows.Count, 1).End(xlUp).Row

lRowLoop = 0

Do While lRowLoop * 5 < lLastRow
    shtOrg.Cells(lRowLoop * 5 + 1, 1).Resize(5).Copy
    shtDest.Cells(lRowLoop + 1, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    lRowLoop = lRowLoop + 1
Loop

With application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Open in new window


Thomas
0
 
LVL 2

Author Comment

by:axessJosh
ID: 39957482
Where do i put that code?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39957507
Open your workbook and go to the visual basic editor (Alt + F11). Insert \ Module from the insert menu,

Copy the code in said module.

Run using F5 in the Visual Basic Editor or Alt+F8 in your workbook.

Thomas
0
 
LVL 2

Author Closing Comment

by:axessJosh
ID: 39957515
very cool!

thanks.

I'll have to some cleaning to the data as I didn't realize there were some Canada addresses which offset the info, but I can see that will work once I normalize everything.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39957522
Glad to help.

Thomas
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question