Transpose (?)

[gcotterl]

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