Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro structure

    The attached workbook consists for 2 pages. Page 1 contains the raw data, page 2 the transformed data, that is the required layout. The data relate to 5 min rainfall depths, in 1/1000mm, i.e. 123 means 0.123 millimeter of rainfall. (I know it sounds ridiculous, but that is how the paper recording strips were OCRred).

    Page 1 The layout of the raw data is as follows:

    1st Row Heading:
    Stormnumber (587) Year (1970) Month (4) StartingDay (26) StaringHour (20) StartingMinute (30) NumberOfData (45)
    The following rows contain the actual data, with a maximum number of 24 data per row. For storm 587 there are 2 data rows, one of 24 data and one of 21 data, 45 data in total. In the second (last row of data related to storm 587), there may be other data beyond 21, to fill the row until 24 data but they are ignored.

    After the 2 data rows of storm 587, data for the next storm are given in a similar way. Note the following:

    1) 5 min periods for which no data are given are dry, i.e. no rainfall
    2) Storm 587 starts at 04/26/1970 20:30 and ends at 04/27/1970 00:10
    3) The number of data rows is not fixed, but determined by the number of data given in the storm header.

    Page 2, transformed data, gives the required data layout.

    I tried to do it by transposing formulas referring to page 1, but it is too difficult because the number of data rows is not constant. Moreover, simply copying the transposed formulas does not work. Hence the whole procedure has to be programmed in VBA. My knowledge of VBA is very weak. Could anybody give some clues and help as to how to go about?

    Thanks in advance
    Teunis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro structure

    See attached workbook. I inserted a worksheet Transformed2 with the intended header and formatting. The macro Transform will fill this sheet.
    Please test thoroughly!

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro structure

    Hans,

    It works well for the sheet I attached. I'll now test it for the complete raw data, some 3000 rows altogether, and come back to you.
    Thanks a lot for the job you did!!

    Regards, Teunis

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Doorn, Netherlands
    Posts
    311
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro structure

    Hans,

    I just tested it on the full file and your code performed beautifully: I got the full 40 000 data or so in a single column.
    Once again, many thanks.

    Regards, Teunis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •