Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting and transposing data (2003)

    You haven't provided any feedback to the reply to <post:=592,744>post 592,744</post:> yet. That means that nobody knows whether the reply was helpful.

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    selecting and transposing data (2003)

    Edited by HansV to present data in table format

    I have a file of sampling results in which the analytical data is vertical, so that there are multiple rows with the same data except for the analyte and the sample results. There are 57 columns and 10583 rows of data, following is an excerpt.

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>1</td><td>LOC_CODE</td><td>LOC_DESC</td><td>SAMP_RND</td><td>SAMP_ID</td><td>ANALYTE</td><td>RESULT</td><td align=center>2</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>pH</td><td align=right>7.45</td><td align=center>3</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Temperature</td><td align=right>13.40</td><td align=center>4</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Strontium Total</td><td align=right>0.17</td><td align=center>5</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Vanadium, Total</td><td align=right><0.003</td><td align=center>6</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Tin, Total</td><td align=right><0.01</td><td align=center>7</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Silver, Total</td><td align=right><0.002</td><td align=center>8</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Molybdenum, Total</td><td align=right><0.005</td><td align=center>9</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td>Manganese, Total</td><td align=right><0.003</td></table>
    The SAMP_ID is unique for each sample collected, and both the LOC_CODE and LOC_DESC are unique for each sample site. The results are a mixture of text; e.g. <0.003, and numbers. Not every sample has the same suite of analytes, with some have 3 or 4 analytes and some 20.

    What I needed to do is convert the data so that each individual analyte is a separate column, while keeping all of the other data. For example, I should end up with a row that has all of the data for the First Round of sampling for site R-001, something like this.

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>H</td><td align=center>1</td><td>LOC_CODE</td><td>LOC_DESC</td><td>SAMP_RND</td><td>SAMP_ID</td><td>pH</td><td>Temperature</td><td>Strontium</td><td>Vanadium, Total</td><td align=center>2</td><td>R-001</td><td>Kieffer</td><td>First</td><td align=right>9623120003</td><td align=right>7.45</td><td align=right>13.40</td><td align=right>0.17</td><td align=right><0.003</td></table>
    By concatenating E and D to form

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

    Re: selecting and transposing data (2003)

    Will there always be the same series of analytes for each item (pH, Temperature, ..., Manganese Total) or will this vary from item to item? If the latter, can you provide us with a complete list of analytes that will occur in the data?

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: selecting and transposing data (2003)

    The list of analytes is not the same for all samples. Attached is a list of all reported analytes.
    Thanks.

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

    Re: selecting and transposing data (2003)

    You could prepare a sheet with the correct column headings and use a macro to populate it. In the attached sample workbook, I have named the sheets Original and Transformed. These names are used in the macro, so you'll have to modify the code to correspond to the names you want to use.

    Code:

    Sub Transform()
    Dim wSource As Worksheet
    Dim wTarget As Worksheet
    Dim rSource As Long
    Dim rTarget As Long
    Dim cTarget As Long
    Application.ScreenUpdating = False
    Set wSource = Worksheets("Original")
    Set wTarget = Worksheets("Transformed")
    rTarget = 1
    With wSource.Range("A1").CurrentRegion
    .Sort Key1:=wSource.Range("D1"), Header:=xlYes
    .Sort Key1:=wSource.Range("A1"), _
    Key2:=wSource.Range("B1"), _
    Key3:=wSource.Range("C1"), Header:=xlYes
    End With
    For rSource = 2 To wSource.Range("A65536").End(xlUp).Row
    If Not (wSource.Range("A" & rSource) = wSource.Range("A" & (rSource - 1)) And _
    wSource.Range("B" & rSource) = wSource.Range("B" & (rSource - 1)) And _
    wSource.Range("C" & rSource) = wSource.Range("C" & (rSource - 1)) And _
    wSource.Range("D" & rSource) = wSource.Range("D" & (rSource - 1))) Then
    rTarget = rTarget + 1
    wSource.Range("A" & rSource & "" & rSource).Copy _
    Destination:=wTarget.Range("A" & rTarget)
    End If
    cTarget = wTarget.Range("E1:AQ1").Find(What:=wSource.Range(" E" & rSource), _
    LookIn:=xlValues, LookAt:=xlWhole).Column
    wTarget.Cells(rTarget, cTarget) = wSource.Range("F" & rSource)
    Next rSource
    Application.ScreenUpdating = True
    End Sub

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: selecting and transposing data (2003)

    Thanks! It's a LOT quicker.
    I had other data to bring in; e.g. x.y coordinates for GIS, so I used lookup tables for that.

    Thanks again.

Posting Permissions

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