Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    convert vertical text groups to horizontal columns

    I have a large amount of test data consisting of a date, test name, result, and reference range. This data is laid out in a single column. So I have a Date and under that a Test Name, and under that a Result, and under that a Reference Range and then it repeats. I want to move this data into a database so I need to convert the single column to 4 columns. Any idea how to do that?

    Here is what I have done so far: Below is a sample of the data. Since this is a lab work there may be a dozen tests relating to one date and time.
    Date/Time: 03/01/2016 @ 0900
    Test Name: GLUCOSE
    Result: 336 High
    Reference Range: (70-110)

    Since Access shuffles data around, I began by using Excell to add a column of ID# to maintain the original order of Data. I added a second order ID to keep the date, test, result, and range related. Then a third ID to order the multiple tests under a given date/time.

    My real problem is maintaining relationships in the data. I need the logic on how to do that.
    Last edited by chuckrau; 2016-03-06 at 17:53.
    Thanks
    chuck

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    One way would be to place this formula in the B1 cell and fill across 4 columns (because you have blocks of 4).
    Then, fill down 1/4 of the total number of rows. If you have 16 blocks of 4, fill down 4 rows. If you have 20 blocks of 4, fill down 5 rows.

    =INDIRECT("$A"&((ROW()-1)*4+COLUMN()-1))

    When finished, you might want/need to select the newly transposed data, copy it and do a paste special / values to just get the result rather than the formulas.
    Last edited by kweaver; 2016-03-06 at 17:47.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post
    I failed to mention I only have Office 97. I'm old!
    Thanks
    chuck

  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post
    Actually your formula works in Excel 97 but it does not grab the first 4 entries.
    Thanks
    chuck

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I think the earlier version had INDIRECT, but am not sure...don't run that any longer. Did you try this formula?

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post
    Works perfectly!! Thank you KWeaver!
    Thanks
    chuck

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Seems to work in mine. See the attachment here.
    Attached Files Attached Files

Posting Permissions

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