Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose (?)
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transpose (?)
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 " 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need transpose? help.... | Excel Worksheet Functions | |||
Transpose!! | Excel Discussion (Misc queries) | |||
Transpose (?) | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
Please Help with TRANSPOSE | Excel Worksheet Functions |