Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rows to columns
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
|
|||
|
|||
rows to columns
i replied to yesterday's thread ... lets go back to that thread. I'd posted
some code that didn't give you what was needed...let me take a look at that once more. staying with the original saves others from repeatnng work ...and correcting issues too "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 | |||
Make Excel see columns as rows and rows as columns | Excel Discussion (Misc queries) | |||
how do i paste rows/columns avoiding hidden rows/columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) |