Solved

Convert column of addresses into multiple columns for each address element

Posted on 2014-03-26
5
233 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 500 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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