Transpose (?)

[[email protected]]

Col A has about 45,000 vertical cells containing data. Each "block" of
related data (from "Name" thru "Amount") occupies either 5 or 6 cells.
Each "block" always contains the "Name", "Stock Number", "Street",
"City" and "Amount"; however, the "Age" may not always exist. There
are between 7,500 and 9,000 "blocks".

For example: Col A contains the data; Cols B thru G is the result I'm
looking for:

A B C D E F G

1 JONES JONES A-35 MAIN ST CORONA $45.00
2 A-35
3 MAIN ST
4 CORONA
5 $45.00
6 SMITH SMITH 24 A-365 PINE AVE FONTANA $123.08
7 24
8 A-365
9 PINE AVE
10 FONTANA
11 $123.08
12 ADAMS ADAMS 56 D-5989 FIRST ST SEATTLE$4,516.08
13 56
14 D-5989
15 FIRST ST
16 SEATTLE
17 $4,516.08
18 JOHN JOHN A-2 THIRD DR ATLANTA $1.58
19 A-2
20 THIRD DR
21 ATLANTA
22 $1.58
23 PETERS PETERS D-247 DOVE DR CHICAGO $3.02
24 D-247
25 DOVE DR
26 CHICAGO
27 $673.02
28 ALVERS ALVERS 65 Q-2456 2ND ST PHOENIX $6,304.59
29 65
30 Q-2456
31 2ND ST
32 PHOENIX
33 $6,304.59

How can I "transpose" each "blocks" of related data horizontally from
Cols B thru G?

(Sorry if the data in the columns don't align; if a fixed (monospace)
font was available, the example would look better).

[Chris Marlow]

Hi,

The following works to a point, the only issue would be where the age is
missing the cells wont align & relies on the contents not just the format of
the amount field being $.

Its a start.

Regards,

Chris.

Public Sub Reformat()

Dim lRowCopy As Long
Dim lRowPaste As Long
Dim lColumnPaste As Long

lRowCopy = 1
lRowPaste = 1
lColumnPaste = 1

Do Until IsEmpty(Sheets(1).Cells(lRowCopy, 1))

If Left(Sheets(1).Cells(lRowCopy, 1), 1) = "$" Then

Sheets(2).Cells(lRowPaste, lColumnPaste) =
Sheets(1).Cells(lRowCopy, 1)
lColumnPaste = 1
lRowPaste = lRowPaste + 1

Else

Sheets(2).Cells(lRowPaste, lColumnPaste) =
Sheets(1).Cells(lRowCopy, 1)
lColumnPaste = lColumnPaste + 1

End If

lRowCopy = lRowCopy + 1

Loop

End Sub

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"[email protected]" wrote:

> Col A has about 45,000 vertical cells containing data. Each "block" of
> related data (from "Name" thru "Amount") occupies either 5 or 6 cells.
> Each "block" always contains the "Name", "Stock Number", "Street",
> "City" and "Amount"; however, the "Age" may not always exist. There
> are between 7,500 and 9,000 "blocks".
>
> For example: Col A contains the data; Cols B thru G is the result I'm
> looking for:
>
> A B C D E F G
>
> 1 JONES JONES A-35 MAIN ST CORONA $45.00
> 2 A-35
> 3 MAIN ST
> 4 CORONA
> 5 $45.00
> 6 SMITH SMITH 24 A-365 PINE AVE FONTANA $123.08
> 7 24
> 8 A-365
> 9 PINE AVE
> 10 FONTANA
> 11 $123.08
> 12 ADAMS ADAMS 56 D-5989 FIRST ST SEATTLE$4,516.08
> 13 56
> 14 D-5989
> 15 FIRST ST
> 16 SEATTLE
> 17 $4,516.08
> 18 JOHN JOHN A-2 THIRD DR ATLANTA $1.58
> 19 A-2
> 20 THIRD DR
> 21 ATLANTA
> 22 $1.58
> 23 PETERS PETERS D-247 DOVE DR CHICAGO $3.02
> 24 D-247
> 25 DOVE DR
> 26 CHICAGO
> 27 $673.02
> 28 ALVERS ALVERS 65 Q-2456 2ND ST PHOENIX $6,304.59
> 29 65
> 30 Q-2456
> 31 2ND ST
> 32 PHOENIX
> 33 $6,304.59
>
> How can I "transpose" each "blocks" of related data horizontally from
> Cols B thru G?
>
> (Sorry if the data in the columns don't align; if a fixed (monospace)
> font was available, the example would look better).
>
>