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.

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)


On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

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.

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

On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

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.

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


"Gord Dibben" wrote:

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

On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

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.

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"?

"Chip Pearson" wrote:


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)


On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

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.

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

On Wed, 15 Oct 2008 10:11:01 -0700, Ratatat
wrote:

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


"Gord Dibben" wrote:

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

On Wed, 15 Oct 2008 06:44:01 -0700, Ratatat
wrote:

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.