Solved

Convert column of addresses into multiple columns for each address element

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

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 …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

813 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

11 Experts available now in Live!

Get 1:1 Help Now