A
Anna
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.
I’m 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.
I’ve tried transpose in a macro, ended in a real mess! Please help!
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.
I’m 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.
I’ve tried transpose in a macro, ended in a real mess! Please help!