like a transpose

[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!

 

[Patrick Molloy]

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]

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]

change "3200" to "300"
sorry - thats a typo

when a row starts with 300, the loop moved column to column collecting data