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:

Sample ID	Sample Date	Chemical Name	Result
TP-01-4.5	03-Oct-13	Benzene	0.0228 U
TP-01-4.5	03-Oct-13	Ethylbenzene	0.0341 U
TP-01-4.5	03-Oct-13	Toluene	0.0228 U
TP-01-4.5	03-Oct-13	mp-Xylene	0.0228 U
TP-01-4.5	03-Oct-13	o-Xylene	0.0228 U
TP-01-4.5	03-Oct-13	cis-12-Dichloroethene	0.0228 U
TP-01-4.5	03-Oct-13	trans-12-Dichloroethene	0.0228 U
TP-01-4.5	03-Oct-13	Tetrachloroethene (PCE)	0.121
TP-01-4.5	03-Oct-13	Trichloroethene (TCE)	0.0228 U
TP-01-4.5	03-Oct-13	Vinyl chloride	0.00228 U
TP-01-8.5	04-Oct-13	Benzene	0.0228 U
TP-01-8.5	04-Oct-13	Ethylbenzene	0.0343 U
TP-01-8.5	04-Oct-13	Toluene	0.0228 U
TP-01-8.5	04-Oct-13	mp-Xylene	0.0228 U
TP-01-8.5	04-Oct-13	o-Xylene	0.0228 U
TP-01-8.5	04-Oct-13	cis-12-Dichloroethene	0.0228 U
TP-01-8.5	04-Oct-13	trans-12-Dichloroethene	0.0228 U
TP-01-8.5	04-Oct-13	Tetrachloroethene (PCE)	0.32
TP-01-8.5	04-Oct-13	Trichloroethene (TCE)	0.0228 U
TP-01-8.5	04-Oct-13	Vinyl chloride	0.00228 U
TP-02-4.5	05-Oct-13	Benzene	0.0291 U
TP-02-4.5	05-Oct-13	Ethylbenzene	0.0437 U
TP-02-4.5	05-Oct-13	Toluene	0.0291 U
TP-02-4.5	05-Oct-13	mp-Xylene	0.0291 U
TP-02-4.5	05-Oct-13	o-Xylene	0.0291 U
TP-02-4.5	05-Oct-13	cis-12-Dichloroethene	0.0291 U
TP-02-4.5	05-Oct-13	trans-12-Dichloroethene	0.0291 U
TP-02-4.5	05-Oct-13	Tetrachloroethene (PCE)	1.18
TP-02-4.5	05-Oct-13	Trichloroethene (TCE)	0.0291 U
TP-02-4.5	05-Oct-13	Vinyl chloride	0.00291 U

<tbody>
</tbody>

I need it to look like this:

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

<tbody>
</tbody>

 

[Jerry Sullivan]

Hi SeattleTimeBandit,

You could do this using an SQL Query. Assuming you define a static Named Range "MyData" for your raw data the query could be....

TRANSFORM Max(Result)
SELECT `Chemical Name`
FROM MyData
GROUP BY `Chemical Name`
PIVOT `Sample ID` & ' - ' & `Sample Date`

You can return this query to a Table, then just refresh the query when your Raw Data changes.

If you haven't used SQL from Excel, just identify which version of Excel you are using and I'll provide some instructions.

 

[seattletimebandit]

Actually not well versed in SQL. played a bit with it in MS Access/ASP, but could certainly use some instruction.

I mostly use Excel 2007, but do have 2003 and 2010. Let's use 2007.

Thanks!

 

[Jerry Sullivan]

Below are some instructions on how to use MSQuery to create a data source for a Table using xl2007 or xl2010.

The titles in blue font below represent the titles of the dialog windows that you'll see as you proceed.

Define a Named Range "MyData" for the Raw Data range including its header.
Save and Close the workbook (these instructions assume it is named "MyBook1.xlsx"

Open a NEW blank workbook

From the Ribbon:
Data> From Other Sources > From Microsoft Query

Choose Data Source
Excel Files* > OK > (Browse to your file "MyBook1.xlsx" and select it) > OK

Query Wizard - Choose Columns:
Select the Name "MyData" then click the ">" button
Next >

Query Wizard - Filter Data:
Click the cancel button to short cut wizard steps that are not needed
You'll see a prompt "Do you want to continue editing this query in Microsoft Query?
Click Yes

A Microsoft Query Window will open
Click on the menubar button labeled "SQL"

Copy and Paste this Query Text into the SQL statement: box (replacing the existing statement)

TRANSFORM Max(Result)
SELECT `Chemical Name`
FROM MyData
GROUP BY `Chemical Name`
PIVOT `Sample ID` & ' - ' & `Sample Date`

Click OK
If you get the message "SQL Query can't be represented graphically, Continue anyway?" Click OK.

Microsoft Query
Click on the menubar button with the Exit Door image

Import Data
Click options: Table, New Worksheet
OK

That should import your table. You can Cut and Paste this Table back into your MyBook1.xlsx file.
When you update your raw data, you just need to resize the Named Range "MyData", then Refresh your Table

 

[MickG]

Another option:-
Results start "F1"

[COLOR=Navy]Sub[/COLOR] MG01Nov40
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dic [COLOR=Navy]As[/COLOR] Object
 
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("C2"), Range("c" & Rows.Count).End(xlUp))
 [COLOR=Navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
            [COLOR=Navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
                [COLOR=Navy]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR=Navy]End[/COLOR] If
        
        [COLOR=Navy]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, -1).Value & Dn.Offset(, -2).Value) [COLOR=Navy]Then[/COLOR]
            Dic(Dn.Value).Add (Dn.Offset(, -1).Value & "," & Dn.Offset(, -2).Value), Dn.Offset(, 1)
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Dn
   [COLOR=Navy]Dim[/COLOR] k [COLOR=Navy]As[/COLOR] Variant
   [COLOR=Navy]Dim[/COLOR] p
   [COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
   [COLOR=Navy]Dim[/COLOR] ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
    
   c = 2
    Cells(1, "F") = "Chemical Name"
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] k [COLOR=Navy]In[/COLOR] Dic.Keys
        ac = 6
        c = c + 1
        Cells(c, "F") = k
            For Each p In Dic(k)
               ac = ac + 1
               Cells(1, ac) = Split(p, ",")(1)
               Cells(2, ac) = Split(p, ",")(0)
               Cells(c, ac) = Dic(k).Item(p)
            [COLOR=Navy]Next[/COLOR] p
    [COLOR=Navy]Next[/COLOR] k




[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[seattletimebandit]

Hey Jerry,

I followed your instructions, but when I came to "SQL Query can't be represented graphically, Continue anyway?" Click OK. However, the message box stayed open until I clicked "Cancel". I finished your instructions and the table imported exactly as it is presented in the original raw data format.

Thoughts?

 

[Jerry Sullivan]

Did a different error message come up when you clicked OK? (like a Syntax error)
It's possible that there are some name mismatches if your file doesn't match my mockup from your OP.

If you click on the SQL icon after Canceling, is the your new SQL text there or did it revert to the original query.

Mick's option is a good VBA alternative.

 

[seattletimebandit]

Mick,

That worked for me, except when I tried to run the code on 600 rows. The debugger stops here (italics/bold):


If Not Dic(Dn.Value).exists(Dn.Offset(, -1).Value & Dn.Offset(, -2).Value) Then
Dic(Dn.Value).Add (Dn.Offset(, -1).Value & "," & Dn.Offset(, -2).Value), Dn.Offset(, 1)

It worked on one extra row than I had in my example table above, but any more than that the code busts. Is your code setting a stop point when counting down the rows? or is there something in my data that causes it to bust? All the rows are the same data records down to the end at row 590.

FYI: There are extra compounds at some point down the rows, 4 extra chemicals are analyzed, but the first compound "Gasoline" doesn't show up until row 424. Will that cause a problem?

Thanks!

stb

 

[seattletimebandit]

Jerry,

No other messages. I re-opened the sql button and it reverted to the original query.

Mick's did work, but only on the data I presented. I need to be able to run the code on more than just the 30 rows in my example. But I'd like to see how the MyQuery function works, it's pretty cool! Another day of learning...

stb

 

[seattletimebandit]

Jerry,

Thinking that my problem using SQL might be the same I had with Mick's VBA (I ran the code on the entire 590 row table), I trimmed my data to the 30 rows as in my example and it worked! Excellent. But as I asked of Mick, how would I do this on various row numbers. Some tables could e 30 rows, some would be 3,000 rows. Thanks btw, nice and clean.

stb (Russell)