Solved

Convert column of addresses into multiple columns for each address element

Posted on 2014-03-26
5
222 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
  • 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now