+ Reply to Thread
Results 1 to 3 of 3

Data transposing

  1. #1
    Registered User
    Join Date
    05-24-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Data transposing

    Hello,

    Being a close-to-novice Excel user, I have come across a problem (or a challenge rather) involving huge sets of data which need to be transposed from rows into columns.

    please see attached workbook. In the "Raw Data" worksheet, you will notice the first two columns being the date and the time that the data was logged. Data was logged every 10 seconds (roughly) for about 491 parameters (see Column C). After every 491 parameters the time changes and continues on for another 491 rows. What I require is:

    1. Only the "tag indices" from 346 to 408 in Column C and the corresponding "Value" in Column D for every time interval.
    2. The above parameters which are currently being tabulated row-wise to be transposed column-wise. See worksheet "Required" where I have shown a sample of how I want the data to be organized.
    3. NOTE: As this data was logged by a data logger, the data is not consistent. Some time intervals (not in this workbook but in others) just stop at "200 tag indices" and jump to the next time interval. Hence the data sorting and transposing is probably best achieved by looking at the "time" rather than the "tag index". In such cases, the values would be ZERO for the tag indices that I am after.

    If there is a quick and easy (being rather hopeful!!) way to do this by a macro or some such thing, it would be great as I have about a 100 such workbooks that I need to sort!!! Help highly appreciated with loads of thanks in advance.

    If something is unclear, please let me know.

    Sincerely,
    Ananth
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Data transposing assistance required

    let the sheet "required" remain as reference
    insert another sheet and call it "sheet2"

    now copy the three macros in the module
    run onlly "test" repeat "test" only.

    if you want to undo run undo and you can again run 'test" to recheck

    the macros are

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.



    Please Login or Register  to view this content.
    lls.Clear
    End Sub


    I am also sending the file now called "ananth". it .xlsm file that is macro enable excel 2007 file see sheet2.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-24-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Smile Re: Data transposing

    Hi Venkat,

    Thanks a ton. Initial results are great and the code seems to work like a charm. You are a champion.

    Will let you know if there are any issues when I encounter inconsistent data, but looking at the code, I am guessing it allows for that, so I should be fine.

    Thanks once again. I owe you one.

    Cheers

    Ananth

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1