Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
like a transpose
I have .csv file. There are 4 chunks of data going down the page.
The first column contains a €˜code. Format of the data: 200 ACC005678 blah 3 M1 N1 2345 Kg 15 300 20090312 5.23 0 0 0 0 0 0 0 300 20090313 0 2.01 0 0 0 0 8.12 0 300 20090314 0 0 0 0 4.56 0 0 0 .. .. ..where code = 300, the data keeps going to the right until column CT, and keeps going down until it hits another chunk of data. .. .. 200 ACC005678 blah 1 P1 N1 2345 Pound 15 300 20090312 2.96 2.784 2.032 2.16 1.728 2.304 1.984 14.88 300 20090313 2.832 2.8 2.432 1.808 1.344 2.144 1.264 10.96 300 20090314 1.168 1.84 1.712 1.968 1.296 2.096 1.104 10.08 .. .. 2 more chunks of data follows. Code = 200 tells me about the type of information that follows. There are only 9 columns when the code = 200. Eg. code, accountNo, some data, stream, stream type, some data, some data, unit. Im interested in accountNo, stream, stream type, unit. So in the first chunk of data above, stream number is 3, stream type is M1, unit is Kg. Code = 300 contain the rows of data I want. Column B represents a date. Eg. 20090312, is to be interpreted as yyyymmdd. There is one row of data per day per chunk of data. The output I want: AccNo Date Time Kg Pound Metres Miles ACC005678 20090312 00:15 5.23 2.96 0 1.2 ACC005678 20090312 00:30 0 2.784 0 5.7 ACC005678 20090312 00:45 0 2.032 0 0.2 ACC005678 20090312 01:00 0 2.16 0 0.6 ACC005678 20090312 01:15 0 1.728 0 3.1 ACC005678 20090312 01:30 0 2.304 0 5.5 .. .. Time goes from 00:15 to 00:00. Basically I want the 96 values that were going across to go down, keep going down for all the days. The 4 chunks of data now form columns of data. Ive tried transpose in a macro, ended in a real mess! Please help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
like a transpose
so are you saying that th edata is in chunks where the first record starts
with 200 an dthe next three all start with 300, or are there any number of records before th e start of the next 'chunk' ? so your loop gets the AccNo for the first '200' roww, then collects data from the subsequent '300' rows until the next chunk begins again with a '200' row? this might get you started: , if not, send me your work book and I'll try again Option Explicit Sub Gatherdata() Dim record As Long Dim account As Long Dim wsResult As Worksheet Dim wsActive As Worksheet Dim resultCol As Long Dim resultRow As Long Set wsActive = ActiveSheet Set wsResult = Worksheets.Add record = 2 ' initialise, first is headers so start at 2 With wsActive Do Until .Cells(record, 1) = "" If .Cells(record, 1) = "200" Then resultCol = resultCol + 1 resultRow = 2 wsResult.Cells(1, resultCol) = .Cells(record, 2) ElseIf .Cells(record, 1) = "3200" Then resultCol = 2 Do Until .Cells(record, resultCol) = "" wsResult.Cells(resultRow, resultCol) = .Cells(record, resultCol) resultCol = resultCol + 1 Loop End If record = record + 1 Loop End With End Sub "Anna" wrote: I have .csv file. There are 4 chunks of data going down the page. The first column contains a €˜code. Format of the data: 200 ACC005678 blah 3 M1 N1 2345 Kg 15 300 20090312 5.23 0 0 0 0 0 0 0 300 20090313 0 2.01 0 0 0 0 8.12 0 300 20090314 0 0 0 0 4.56 0 0 0 . . .where code = 300, the data keeps going to the right until column CT, and keeps going down until it hits another chunk of data. . . 200 ACC005678 blah 1 P1 N1 2345 Pound 15 300 20090312 2.96 2.784 2.032 2.16 1.728 2.304 1.984 14.88 300 20090313 2.832 2.8 2.432 1.808 1.344 2.144 1.264 10.96 300 20090314 1.168 1.84 1.712 1.968 1.296 2.096 1.104 10.08 . . 2 more chunks of data follows. Code = 200 tells me about the type of information that follows. There are only 9 columns when the code = 200. Eg. code, accountNo, some data, stream, stream type, some data, some data, unit. Im interested in accountNo, stream, stream type, unit. So in the first chunk of data above, stream number is 3, stream type is M1, unit is Kg. Code = 300 contain the rows of data I want. Column B represents a date. Eg. 20090312, is to be interpreted as yyyymmdd. There is one row of data per day per chunk of data. The output I want: AccNo Date Time Kg Pound Metres Miles ACC005678 20090312 00:15 5.23 2.96 0 1.2 ACC005678 20090312 00:30 0 2.784 0 5.7 ACC005678 20090312 00:45 0 2.032 0 0.2 ACC005678 20090312 01:00 0 2.16 0 0.6 ACC005678 20090312 01:15 0 1.728 0 3.1 ACC005678 20090312 01:30 0 2.304 0 5.5 . . Time goes from 00:15 to 00:00. Basically I want the 96 values that were going across to go down, keep going down for all the days. The 4 chunks of data now form columns of data. Ive tried transpose in a macro, ended in a real mess! Please help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
like a transpose
Hi Patrick,
When I run the macro, I get the account number in A1, B1, C1, D1. The answer to your questions is yes. I only need to worry abt code 200, and 300, ignore all else. Thanks. "Patrick Molloy" wrote: so are you saying that th edata is in chunks where the first record starts with 200 an dthe next three all start with 300, or are there any number of records before th e start of the next 'chunk' ? so your loop gets the AccNo for the first '200' roww, then collects data from the subsequent '300' rows until the next chunk begins again with a '200' row? this might get you started: , if not, send me your work book and I'll try again Option Explicit Sub Gatherdata() Dim record As Long Dim account As Long Dim wsResult As Worksheet Dim wsActive As Worksheet Dim resultCol As Long Dim resultRow As Long Set wsActive = ActiveSheet Set wsResult = Worksheets.Add record = 2 ' initialise, first is headers so start at 2 With wsActive Do Until .Cells(record, 1) = "" If .Cells(record, 1) = "200" Then resultCol = resultCol + 1 resultRow = 2 wsResult.Cells(1, resultCol) = .Cells(record, 2) ElseIf .Cells(record, 1) = "3200" Then resultCol = 2 Do Until .Cells(record, resultCol) = "" wsResult.Cells(resultRow, resultCol) = .Cells(record, resultCol) resultCol = resultCol + 1 Loop End If record = record + 1 Loop End With End Sub "Anna" wrote: I have .csv file. There are 4 chunks of data going down the page. The first column contains a €˜code. Format of the data: 200 ACC005678 blah 3 M1 N1 2345 Kg 15 300 20090312 5.23 0 0 0 0 0 0 0 300 20090313 0 2.01 0 0 0 0 8.12 0 300 20090314 0 0 0 0 4.56 0 0 0 . . .where code = 300, the data keeps going to the right until column CT, and keeps going down until it hits another chunk of data. . . 200 ACC005678 blah 1 P1 N1 2345 Pound 15 300 20090312 2.96 2.784 2.032 2.16 1.728 2.304 1.984 14.88 300 20090313 2.832 2.8 2.432 1.808 1.344 2.144 1.264 10.96 300 20090314 1.168 1.84 1.712 1.968 1.296 2.096 1.104 10.08 . . 2 more chunks of data follows. Code = 200 tells me about the type of information that follows. There are only 9 columns when the code = 200. Eg. code, accountNo, some data, stream, stream type, some data, some data, unit. Im interested in accountNo, stream, stream type, unit. So in the first chunk of data above, stream number is 3, stream type is M1, unit is Kg. Code = 300 contain the rows of data I want. Column B represents a date. Eg. 20090312, is to be interpreted as yyyymmdd. There is one row of data per day per chunk of data. The output I want: AccNo Date Time Kg Pound Metres Miles ACC005678 20090312 00:15 5.23 2.96 0 1.2 ACC005678 20090312 00:30 0 2.784 0 5.7 ACC005678 20090312 00:45 0 2.032 0 0.2 ACC005678 20090312 01:00 0 2.16 0 0.6 ACC005678 20090312 01:15 0 1.728 0 3.1 ACC005678 20090312 01:30 0 2.304 0 5.5 . . Time goes from 00:15 to 00:00. Basically I want the 96 values that were going across to go down, keep going down for all the days. The 4 chunks of data now form columns of data. Ive tried transpose in a macro, ended in a real mess! Please help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
like a transpose
change "3200" to "300"
sorry - thats a typo when a row starts with 300, the loop moved column to column collecting data "Anna" wrote: Hi Patrick, When I run the macro, I get the account number in A1, B1, C1, D1. The answer to your questions is yes. I only need to worry abt code 200, and 300, ignore all else. Thanks. "Patrick Molloy" wrote: so are you saying that th edata is in chunks where the first record starts with 200 an dthe next three all start with 300, or are there any number of records before th e start of the next 'chunk' ? so your loop gets the AccNo for the first '200' roww, then collects data from the subsequent '300' rows until the next chunk begins again with a '200' row? this might get you started: , if not, send me your work book and I'll try again Option Explicit Sub Gatherdata() Dim record As Long Dim account As Long Dim wsResult As Worksheet Dim wsActive As Worksheet Dim resultCol As Long Dim resultRow As Long Set wsActive = ActiveSheet Set wsResult = Worksheets.Add record = 2 ' initialise, first is headers so start at 2 With wsActive Do Until .Cells(record, 1) = "" If .Cells(record, 1) = "200" Then resultCol = resultCol + 1 resultRow = 2 wsResult.Cells(1, resultCol) = .Cells(record, 2) ElseIf .Cells(record, 1) = "3200" Then resultCol = 2 Do Until .Cells(record, resultCol) = "" wsResult.Cells(resultRow, resultCol) = .Cells(record, resultCol) resultCol = resultCol + 1 Loop End If record = record + 1 Loop End With End Sub "Anna" wrote: I have .csv file. There are 4 chunks of data going down the page. The first column contains a €˜code. Format of the data: 200 ACC005678 blah 3 M1 N1 2345 Kg 15 300 20090312 5.23 0 0 0 0 0 0 0 300 20090313 0 2.01 0 0 0 0 8.12 0 300 20090314 0 0 0 0 4.56 0 0 0 . . .where code = 300, the data keeps going to the right until column CT, and keeps going down until it hits another chunk of data. . . 200 ACC005678 blah 1 P1 N1 2345 Pound 15 300 20090312 2.96 2.784 2.032 2.16 1.728 2.304 1.984 14.88 300 20090313 2.832 2.8 2.432 1.808 1.344 2.144 1.264 10.96 300 20090314 1.168 1.84 1.712 1.968 1.296 2.096 1.104 10.08 . . 2 more chunks of data follows. Code = 200 tells me about the type of information that follows. There are only 9 columns when the code = 200. Eg. code, accountNo, some data, stream, stream type, some data, some data, unit. Im interested in accountNo, stream, stream type, unit. So in the first chunk of data above, stream number is 3, stream type is M1, unit is Kg. Code = 300 contain the rows of data I want. Column B represents a date. Eg. 20090312, is to be interpreted as yyyymmdd. There is one row of data per day per chunk of data. The output I want: AccNo Date Time Kg Pound Metres Miles ACC005678 20090312 00:15 5.23 2.96 0 1.2 ACC005678 20090312 00:30 0 2.784 0 5.7 ACC005678 20090312 00:45 0 2.032 0 0.2 ACC005678 20090312 01:00 0 2.16 0 0.6 ACC005678 20090312 01:15 0 1.728 0 3.1 ACC005678 20090312 01:30 0 2.304 0 5.5 . . Time goes from 00:15 to 00:00. Basically I want the 96 values that were going across to go down, keep going down for all the days. The 4 chunks of data now form columns of data. Ive tried transpose in a macro, ended in a real mess! Please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with transpose? | Excel Programming | |||
Help using Transpose | Excel Discussion (Misc queries) | |||
Transpose a .csv | Excel Programming | |||
transpose | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |