Transpose data as an array in vba

[seattletimebandit]

Hello,

I have a need to take raw laboratory data presented in columns and transpose as an array into a pivot table-like format. I have 600 rows of data that has a lot of repeating
values (Sample ID, Date, Chemical Name). So I need to loop through each "set" of samples and transpose the Sample ID's and Dates as columns and keep the Chemical Names as rows,
but not repeating the Chemical Names over and over (there only 10 chemicals), the Results will then populate the data field transposed into the appropriate corresponding cells.

I have something that works, but it has the chemical names hard-coded and I would like to be able to do this by transposing as a array, as chemical names can change (some samples may be analyzed for 4 chemicals only, while others might be analyzed for 64 chemicals). To be able to ask the user to select a range to transpose would be icing on the cake.

Thanks in advance!

My raw data looks like this:

[TABLE="width: 824"]
<tbody>[TR]
[TD]Sample ID[/TD]
[TD]Sample Date[/TD]
[TD]Chemical Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0341 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.121[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4.5[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0343 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.32[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-8.5[/TD]
[TD]04-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00228 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0437 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]1.18[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]TP-02-4.5[/TD]
[TD]05-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00291 U[/TD]
[/TR]
</tbody>[/TABLE]

I need it to look like this:

[TABLE="width: 824"]
<tbody>[TR]
[TD] [TABLE="width: 824"]
<tbody>[TR]
[TD]Chemical Name[/TD]
[TD]TP-01-4.5[/TD]
[TD]TP-01-8.5[/TD]
[TD]TP-02-4.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]03-Oct-13[/TD]
[TD]04-Oct-13[/TD]
[TD]05-Oct-13[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.0341 U[/TD]
[TD]0.0343 U[/TD]
[TD]0.0437 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.121[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.0228 U[/TD]
[TD]0.32[/TD]
[TD]1.18[/TD]
[/TR]
[TR]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.00228 U[/TD]
[TD]0.0228 U[/TD]
[TD]0.0291 U[/TD]
[/TR]
[TR]
[TD]Vinyl chloride[/TD]
[TD]0.0228 U[/TD]
[TD]0.00228 U[/TD]
[TD]0.00291 U[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[seattletimebandit]

Well sleuthed!

Thanks! I know just enough to be dangerous! (...and get the job done...sometimes...)

At first your code didn't work, I thought maybe it was because I was at home on Excel 2003, but I read the run-time error message that the jet dbase engine didn't recognize [Sample Date] as a valid field name.

I checked my raw data table and the "Sample Date" header was spelled "Samplie Date". Garbage in...garbage out...

Thanks a whole bunch Jerry. This was a big help and will be great for using an many projects where we need to quickly transpose raw data tables. I will comment the code in my marco/vba library and give both you and Mick the acknowledgement and kudos you deserve.

One last thing, and only if it's not too much trouble for either you or Mick, if some commenting could be added to the code so I can learn the steps the code is going through. (teach a man to fish...and all that).

I'm sure hundreds of other newbie VBA-er's would appreciate it as well.

Thanks again!

Russell

 

[seattletimebandit]

If any one is still monitoring this old thread (Jerry or Mick?), I've been using this code with great success. However, I've found one unforeseen glitch. Some of my data (Sample ID) have a comma (e.g., C04-W(8,25)-3). When running the code, the string splits at the comma, forcing the string to split to the next cell down, replacing the Sample Date string.

Split after transposing:

C04-W(8
25)-3

To prevent this, I've been replacing the "," with a "-", but would like to retain the comma.

Thoughts? I see in the code the following:

For Each p In Dic(k)
               Cells(1, .Item(p) + 6) = Split(p, ",")(0)
               Cells(2, .Item(p) + 6) = Split(p, ",")(1)
               Cells(c, .Item(p) + 6) = Dic(k).Item(p)
            Next p

I assume this is where the split string is occurring. Is there a way to retain the comma and keep the string whole in once cell and keeping the Sample Date cell?

Also I've found that I would like to add another column. Between Sample ID and Sample Date, I want to add a column titled "Lab Report ID" which would transpose to the proper place between the two cells (Sample ID on top, Sample Date below).

Example of new raw data table format:

[table="width: 600"]
[tr]
[td]Sample_ID[/td]
[td]Lab_Report_ID[/td]
[td]Sample_Date[/td]
[td]Chemical Name[/td]
[td]Result[/td]
[/tr]
[tr]
[td]SP-2PCS-2013100[/td]
[td]1310023[/td]
[td]02-Oct-13[/td]
[td]m,p-Xylene[/td]
[td]0.324[/td]
[/tr]
[/table]


Example of new transposed table format:

[table="width: 500"]
[tr]
[td] [/td]
[td][/td]
[/tr]
[tr]
[td]Chemical Name[/td]
[td]SP-2PCS-2013100[/td]
[/tr]
[tr]
[td][/td]
[td]1310023[/td]
[/tr]
[tr]
[td] [/td]
[td]02-Oct-13[/td]
[/tr]
[tr]
[td]m,p-Xylene[/td]
[td]0.324[/td]
[/tr]
[/table]



Thanks in advance!

Russell

 

[MickG]

With regards to the Comma, if you add to each comma in the code(6 places), a space :- from "," to this ", " then this should not be confused with just a basic comma in your data.

Your data and results does not seem to follow the format of original code results, even accounting for the extra column.
Could you show a example (before and after) with more rows to give a better overall picture.

 

[seattletimebandit]

MickG,

Here is the code:

Sub TransposeLabData()Dim dn      As Range
Dim Rng     As Range
Dim Dic     As Object
Dim col     As Long


response = MsgBox("This macro will transpose the raw data table in Columns A-D")
response = MsgBox("Would you like to proceed?", vbQuestion + vbYesNo + vbDefaultButton2, "Continue?")
    If response = vbYes Then
    
    On Error GoTo Canceled 'exit macro if user clicks Cancel button

Set Rng = Range(Range("C2"), Range("c" & Rows.Count).End(xlUp))
 With CreateObject("Scripting.Dictionary")
 .CompareMode = 1
 For Each dn In Rng.Offset(, -2)
    If Not .exists(dn.Value & "," & dn.Offset(, 1)) Then
        col = col + 1
        .Add (dn.Value & "," & dn.Offset(, 1)), col
    End If
 Next dn
 
 
 Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   For Each dn In Rng
            If Not Dic.exists(dn.Value) Then
                Set Dic(dn.Value) = CreateObject("Scripting.Dictionary")
                
            End If
        
       If Not Dic(dn.Value).exists(dn.Offset(, -2).Value & "," & dn.Offset(, -1).Value) Then
            Dic(dn.Value).Add (dn.Offset(, -2).Value & "," & dn.Offset(, -1).Value), dn.Offset(, 1)
        End If
    Next dn
   Dim k As Variant
   Dim p
   Dim c As Long
 
    c = 2
    Cells(1, "F") = "Chemical Name"
    For Each k In Dic.Keys
        c = c + 1
        Cells(c, "F") = k
            For Each p In Dic(k)
               Cells(1, .Item(p) + 6) = Split(p, ",")(0)
               Cells(2, .Item(p) + 6) = Split(p, ",")(1)
               Cells(c, .Item(p) + 6) = Dic(k).Item(p)
            Next p
   Next k
End With
End If


Canceled:
End Sub

Here is the table before running the code:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 755"]
<tbody>[TR]
[TD]Sample ID[/TD]
[TD]Sample Date[/TD]
[TD]Chemical Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0341 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.121[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00228 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0395 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.484[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]31.9[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.865[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00263 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0347 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.387[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00231 U[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Here it is after running the code:
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 350"]
<tbody>[TR]
[TD][TABLE="width: 482"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 482"]
<tbody>[TR]
[TD]Chemical Name[/TD]
[TD]TP-01-4[/TD]
[TD]TP-02-8[/TD]
[TD]TP-03-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/3/2013[/TD]
[TD]10/3/2013[/TD]
[TD]10/3/2013[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.0341 U[/TD]
[TD]0.0395 U[/TD]
[TD]0.0347 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[TD]0.484[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.121[/TD]
[TD]31.9[/TD]
[TD]0.387[/TD]
[/TR]
[TR]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0228 U[/TD]
[TD]0.865[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]Vinyl chloride[/TD]
[TD]0.00228 U[/TD]
[TD]0.00263 U[/TD]
[TD]0.00231 U[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Need to modify code to achieve the additional column (Lab Report ID):



[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 785"]
<tbody>[TR]
[TD]Sample ID[/TD]
[TD]Lab Report ID[/TD]
[TD]Sample Date[/TD]
[TD]Chemical Name[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0341 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.121[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0228 U[/TD]
[/TR]
[TR]
[TD]TP-01-4[/TD]
[TD]131231[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00228 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0395 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.484[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0263 U[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]31.9[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.865[/TD]
[/TR]
[TR]
[TD]TP-02-8[/TD]
[TD]131242[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00263 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]Benzene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]Ethylbenzene[/TD]
[TD]0.0347 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]Toluene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]mp-Xylene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]o-Xylene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.387[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]TP-03-4[/TD]
[TD]131211[/TD]
[TD]03-Oct-13[/TD]
[TD]Vinyl chloride[/TD]
[TD]0.00231 U[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]






Transposed table after code (with Lab Report ID highlighted for clarity):

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 482"]
<tbody>[TR]
[TD]Chemical Name[/TD]
[TD]TP-01-4.5[/TD]
[TD]TP-02-8[/TD]
[TD]TP-03-4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/3/2013[/TD]
[TD]10/3/2013[/TD]
[TD]10/3/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]131231[/TD]
[TD]131242[/TD]
[TD]131211[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.0341 U[/TD]
[TD]0.0395 U[/TD]
[TD]0.0347 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]mp-Xylene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]cis-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[TD]0.484[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]trans-12-Dichloroethene[/TD]
[TD]0.0228 U[/TD]
[TD]0.0263 U[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]Tetrachloroethene (PCE)[/TD]
[TD]0.121[/TD]
[TD]31.9[/TD]
[TD]0.387[/TD]
[/TR]
[TR]
[TD]Trichloroethene (TCE)[/TD]
[TD]0.0228 U[/TD]
[TD]0.865[/TD]
[TD]0.0231 U[/TD]
[/TR]
[TR]
[TD]Vinyl chloride[/TD]
[TD]0.00228 U[/TD]
[TD]0.00263 U[/TD]
[TD]0.00231 U[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

Sub TransposeLabData()
Dim dn          As Range
Dim Rng         As Range
Dim Dic         As Object
Dim col         As Long
Dim Response    As String
Dim k           As Variant
Dim p           As Variant
Dim c           As Long
Response = MsgBox("This macro will transpose the raw data table in Columns A-D")
Response = MsgBox("Would you like to proceed?", vbQuestion + vbYesNo + vbDefaultButton2, "Continue?")
    If Response = vbYes Then
    
    On Error GoTo Canceled 'exit macro if user clicks Cancel button


Set Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
 With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
 
 For Each dn In Rng.Offset(, -3)
    If Not .exists(dn.Value & "," & dn.Offset(, 1) & "," & dn.Offset(, 2)) Then
        col = col + 1
        .Add (dn.Value & "," & dn.Offset(, 1) & "," & dn.Offset(, 2)), col
    End If
 Next dn
 
 
 Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   For Each dn In Rng
            If Not Dic.exists(dn.Value) Then
                Set Dic(dn.Value) = CreateObject("Scripting.Dictionary")
            End If
                Set Dic(dn.Value)(dn.Offset(, -3).Value & "," & dn.Offset(, -2).Value & "," & dn.Offset(, -1).Value) = dn
    Next dn
   
c = 3
    Cells(1, "F") = "Chemical Name"
    For Each k In Dic.Keys
        c = c + 1
        Cells(c, "F") = k
            For Each p In Dic(k)
               Cells(1, .Item(p) + 6) = Split(p, ",")(0)
               Cells(2, .Item(p) + 6) = Split(p, ",")(2)
               Cells(3, .Item(p) + 6) = Split(p, ",")(1)
               Cells(c, .Item(p) + 6) = Dic(k).Item(p).Offset(, 1)
            Next p
   Next k
End With
End If


Canceled:
End Sub

 

[seattletimebandit]

MickG,

Brilliant! Worked perfectly. And the changing of the ", " to stop the splitting of the Sample IDs with comma's worked as well. Now that you made those additions, I can see where/how the Offsets were used for each field. My challenge now is to reverse the table by having the Sample IDs down the left field and the Chemical Names across the top row (I interchange as needed for reporting purposes, sometimes the reverse is more applicable to reduce the width of a table). My first way was to copy the transposed table and Paste Special ->Transpose, which works perfectly and is rather easy, but I'm going to see if I can give it ago on my own by modifying the current code format. If I get stuck, or have data scrambled all over the place, I may be giving you a "call" once more.

My co-workers are really appreciating your help with this. In the past we have hand entered data like this or spent WAY too much time cutting and pasting the raw data from the laboratory's data spreadsheet.

Thanks again!

Russell