Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert file from column to columns using delimiter
I have a file that has over 2000 entries in column A. Several lines form a
group, separated by a line with " ^ ". I would like to restructure on either the same page or a new page with the values from the first row after the ^ to the next ^ transposed. Here's a sample of the data. Most of the groups have 6 rows (not counting the ^). D02/19/2008 T0.05 PMI Bank MInterest LBank Charges ^ D02/28/2008 T200.00 CX PGene MOpening Deposit LShort Term Loan ^ D03/07/2008 T-93.84 CX PBank Charge MPrint Checks LBank Charges ^ D04/01/2008 T340.26 CX PPavillion MService LWiFi ^ D04/03/2008 T5,004.03 CX PGene MLoan LShort Term Loan ^ Here is what the first four groups should be after translation: D02/19/2008 T0.05 PMI Bank MInterest LBank Charges ^ D02/28/2008 T200.00 CX PGene MOpening Deposit LShort Term Loan ^ D03/07/2008 T-93.84 CX PBank Charge MPrint Checks LBank Charges ^ D04/01/2008 T340.26 CX PPavillion MService LWiFi ^ D04/03/2008 T5,004.03 CX PGene MLoan LShort Term Loan ^ Gene Augustin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert file from column to columns using delimiter
Try this macro:
Sub reConfigure() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") s1.Activate n = Cells(Rows.Count, 1).End(xlUp).Row i = 1 j = 1 For k = 1 To n v = Cells(k, 1) If v < "^" Then s2.Cells(i, j).Value = v j = j + 1 Else i = i + 1 j = 1 End If Next End Sub The code assumes that the data is in column A of Sheet1. Adjust to suit your needs. -- Gary''s Student - gsnu2007L "Gene Augustin" wrote: I have a file that has over 2000 entries in column A. Several lines form a group, separated by a line with " ^ ". I would like to restructure on either the same page or a new page with the values from the first row after the ^ to the next ^ transposed. Here's a sample of the data. Most of the groups have 6 rows (not counting the ^). D02/19/2008 T0.05 PMI Bank MInterest LBank Charges ^ D02/28/2008 T200.00 CX PGene MOpening Deposit LShort Term Loan ^ D03/07/2008 T-93.84 CX PBank Charge MPrint Checks LBank Charges ^ D04/01/2008 T340.26 CX PPavillion MService LWiFi ^ D04/03/2008 T5,004.03 CX PGene MLoan LShort Term Loan ^ Here is what the first four groups should be after translation: D02/19/2008 T0.05 PMI Bank MInterest LBank Charges ^ D02/28/2008 T200.00 CX PGene MOpening Deposit LShort Term Loan ^ D03/07/2008 T-93.84 CX PBank Charge MPrint Checks LBank Charges ^ D04/01/2008 T340.26 CX PPavillion MService LWiFi ^ D04/03/2008 T5,004.03 CX PGene MLoan LShort Term Loan ^ Gene Augustin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert file from column to columns using delimiter
Thanks Gary, works like a charm. On 3/6/09 12:40 PM, in article , "Gary''s Student" wrote: Try this macro: Sub reConfigure() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") s1.Activate n = Cells(Rows.Count, 1).End(xlUp).Row i = 1 j = 1 For k = 1 To n v = Cells(k, 1) If v < "^" Then s2.Cells(i, j).Value = v j = j + 1 Else i = i + 1 j = 1 End If Next End Sub The code assumes that the data is in column A of Sheet1. Adjust to suit your needs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text into multiple columns using a common delimiter | Excel Discussion (Misc queries) | |||
text to columns - break by delimiter | Excel Discussion (Misc queries) | |||
Flat File with @ as delimiter | Excel Discussion (Misc queries) | |||
Tab Delimiter File | Excel Discussion (Misc queries) | |||
How do I specify a specific char delimiter for my file, when I sa. | Excel Discussion (Misc queries) |