Address blocks to columns

[Ratatat]

I have an excel file that contains 500+ address blocks because it was
originally a pdf file that I converted to excel format. My ultimate goal is
to do a mail merge from this file but that's not possible in the format it is
currently. Is there a way to pull the information from that sheet so that it
is organized into columns? Once it's there I can do text to columns to
separate items when necessary but I don't know how to get there.

 

[Chip Pearson]

It depends on how those address blocks are laid out. Are there always
N number of rows per address? You might be able to adapt the following
code. Src is the source data, on sheet 3 starting in A1. Dest is the
destination, on sheet 2 starting in A1. It assumes that each address
block has ROWS_PER_BLOCK rows with no blank lines between the
addresses (an address block may have blank lines, but no blank lines
separate the blocks).

Sub AAA()
Dim Src As Range
Dim Dest As Range
Dim N As Long
Dim LastRow As Long
Const ROWS_PER_BLOCK = 4

Set Src = Worksheets(3).Range("A1")
Set Dest = Worksheets(2).Range("A1")
LastRow = Worksheets(3).Cells(Rows.Count, "A").End(xlUp).Row

Do Until Src.Row > LastRow
For N = 1 To ROWS_PER_BLOCK
Dest(1, N) = Src(N, 1)
Next N
Set Src = Src(ROWS_PER_BLOCK + 1, 1)
Set Dest = Dest(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

Are the address blocks in one column?

Are the elements in a single cell or sets of cells?

Are the blocks consistent in size?

Can you post a sample of the current layout?

Not a file, just a copied sample.


Gord Dibben MS Excel MVP

 

[Ratatat]

Each line is in it's own cell and they are all in one column. Some are 3
lines, some are 4 and there is a blank space between them.

Example:

ABC Development, Inc.
9 ABC Road
Dallas, TX 75201

123 Realty LLC
10 Main St
PO Box 586
Pheonix, AZ 85001

 

[Ratatat]

This does get them into rows but since they are not consistently 4 lines and
there is a line between them the records are getting broken up. Is there a
way to have it just start a new row each time it hits a blank row rather than
defining "ROWS_PER_BLOCK"?

 

[Gord Dibben]

Try this macro from Ken Johnson.

Public Sub TransposePersonalData()
'ken johnson July 29, 2006
'transpose uneven sets of data........must have a blank row between sets
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim i As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
i = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
i = i + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(i, iDataColumn + 1).PasteSpecial Transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Gord