Need Macro to transpose data (MS OS X)

[skfortner]

I have excel file that have the following columns: Sample ID, Analyte Name, Concentration, RSD. These columns are filled down.

I would like the analyte names should be the column headings. Then the rows with the sample ID, Concentration, and RSD - these rows are filled in with elemental concentrations, and rsds associated with those concentrations.

It is not quite a simple transpose, the rows should start over every time the sample name changes. I will be very grateful for any help with this and would gladly send a file to anyone who would help me. Sometimes the number of analytes I sample changes, so if anyone has any ideas on how to write a macro that can do this by recognizing when the analyte name repeats itself (to know when to start a new set of rows), I would be especially appreciative.

 

[Andrew Poulsom]

Welcome to the Board.

I'm not sure why you would want to transpose data that is in first normal form, but could you post a small but representative example of before and after?

 

[skfortner]

I'm having difficulty posting a file- I think it might take me longer to figure out how to link my data than to address managing the data. Thanks for your time though!

 

[Andrew Poulsom]

Just post text tables, using | as the column break indicator.

 

[skfortner]

BEFORE:

Sample ID Analyte Name Conc (Calib) RSD (Conc)
blanka Al 396.153 -0.061671764 3.565806827
blanka Al 308.215 -0.194249893 3.192473704
blanka Al 394.401 -0.049272798 9.68050951
blanka Ca 317.933 -0.587148639 0.134633141
blanka Ca 315.887 -0.620033468 0.262156516
blanka Mg 285.213 -0.205192727 0.195626939
blanka Mg 279.077 -0.072342327 7.089947069
blanka Na 589.592 0.224135264 0.165809314
blanka Na 330.237 -0.117459284 39.07745771
blanka Si 212.412 2.122191608 11.60028097
blanka Si 252.851 2.003578973 10.53081078
blanka Si 221.667 2.268451177 12.08497506
blanka Cd 228.802 -0.010005719 5.149528513
blanka Cd 214.440 -0.008089673 0.323331781
blanka Cu 327.393 -0.007868074 22.72843359
blanka Cu 324.752 -0.01069137 2.902758779
blanka Cu 224.700 -0.01627942 7.582079095
blanka K 766.490 0.033946858 10.29495695
blanka Fe 238.204 -0.011420631 4.276704463
blanka Fe 239.562 -0.007097242 13.3580206
blanka Fe 259.939 -0.005350838 5.572188823
blanka Li 670.784 0.019604144 0.071626859
blanka Li 610.362 0.015201877 11.48329383
blanka Li 460.286 -0.012398549 20.17889095
blanka Mn 257.610 -0.016444402 0.209308771
blanka Mn 260.568 -0.007278132 3.664124497
blanka Ni 231.604 -0.009937828 10.47402766
blanka Sr 407.771 -0.00636489 0.280149274
blanka Sr 421.552 -0.012172292 0.134139134
blanka Sr 460.733 0.02976084 6.009320416
blanka V 310.230 -0.063570939 1.761372189
blanka Zn 206.200 -0.009642332 9.432463034
blanka Zn 213.857 -0.006856481 5.373517002
blanka Ce 413.764 0.005581326 19.7612903
blanka Ce 418.660 -0.00164864 318.1757421
blanka Co 228.616 -0.002715866 23.27322636
blanka Cr 267.716 -0.00319208 33.12599238
blanka Cr 205.560 -0.001375884 45.46690008
blanka Mo 202.031 -0.004192961 15.97475934
blanka Mo 203.845 -0.007985253 75.90898526
blanka Pb 220.353 -0.003971083 154.6504043
blanka Sn 235.485 -0.006787248 115.7011182
blanka U 385.958
blanka U 409.014
blanka Al 396 RAD -0.133624369 3.019373294
blanka Al 308 RAD -0.149614986 11.94503704
blanka Al 394 RAD -0.11922773 21.61738623
blanka Ca 317 RAD -0.469233489 1.785836758
blanka Ca 315 RAD -0.482636175 3.559956563
blanka Mg 285 RAD -0.186862229 0.80417468
blanka Mg 279 RAD -0.206458693 6.324821418
blanka Na 589 RAD -0.0878017 18.82038404
blanka Na 330 RAD -0.344405 15.18645947
blanka Si 212 RAD 1.081582431 14.45815264
blanka Si 252 RAD 1.016239104 2.167807794
10ppmA Al 396.153 11.22718383 0.135066732
10ppmA Al 308.215 11.37794615 0.146150252
10ppmA Al 394.401 10.88863169 0.218727835
10ppmA Ca 317.933 10.74051615 0.09298891
10ppmA Ca 315.887 10.77809998 0.058299355
10ppmA Mg 285.213 11.52798799 0.11394565
10ppmA Mg 279.077 11.1120829 0.141722494
10ppmA Na 589.592 10.12902368 0.295995043
10ppmA Na 330.237 11.20431483 0.513759105
10ppmA Si 212.412 10.60796703 1.051582158
10ppmA Si 252.851 11.2902792 0.088591263
10ppmA Si 221.667 10.69608214 0.890472022
10ppmA Cd 228.802 1.044295038 0.898550125
10ppmA Cd 214.440 1.10396127 0.132703787
10ppmA Cu 327.393 1.055142769 0.225753842
10ppmA Cu 324.752 1.057546572 0.098214032
10ppmA Cu 224.700 1.076572259 0.886718828
10ppmA K 766.490 0.894423477 0.238801063
10ppmA Fe 238.204 1.094935874 0.152898285
10ppmA Fe 239.562 1.015964775 0.873233657
10ppmA Fe 259.939 1.087165104 0.135997264
10ppmA Li 670.784 1.003925573 0.239202274
10ppmA Li 610.362 1.031783381 0.075361282
10ppmA Li 460.286 1.006227122 3.545733262
10ppmA Mn 257.610 1.070410217 0.052175223
10ppmA Mn 260.568 1.077409381 0.072694019
10ppmA Ni 231.604 1.018629474 1.036961856
10ppmA Sr 407.771 1.038480903 0.301133254
10ppmA Sr 421.552 1.054953982 0.125652261
10ppmA Sr 460.733 0.945712852 0.566947666
10ppmA V 310.230 1.063824921 0.327647814
10ppmA Zn 206.200 1.039028301 0.866728702
10ppmA Zn 213.857 1.096757788 0.145482803
10ppmA Ce 413.764 0.107060282 2.208626943
10ppmA Ce 418.660 0.106328712 4.495035369
10ppmA Co 228.616 0.104706496 1.2005623
10ppmA Cr 267.716 0.110760219 2.304996991
10ppmA Cr 205.560 0.113032759 0.796857877
10ppmA Mo 202.031 0.101964003 4.007948716
10ppmA Mo 203.845 0.103298548 3.80801312
10ppmA Pb 220.353 0.1137192 8.310037785
10ppmA Sn 235.485 0.098417656 2.992007355
10ppmA U 385.958
10ppmA U 409.014
10ppmA Al 396 RAD 11.67669013 0.594525002
10ppmA Al 308 RAD 11.74741001 0.263480225
10ppmA Al 394 RAD 11.68255816 0.314405658
10ppmA Ca 317 RAD 10.92242817 0.463942012
10ppmA Ca 315 RAD 10.73999316 0.222094704
10ppmA Mg 285 RAD 11.49693263 0.528620462
10ppmA Mg 279 RAD 11.4661034 0.337431805
10ppmA Na 589 RAD 11.1994885 0.69994913
10ppmA Na 330 RAD 11.21807515 2.624136543
10ppmA Si 212 RAD 11.56228846 1.296175362
10ppmA Si 252 RAD 11.5649816 0.628004726
qout28/09/07_0828 Al 396.153 1.887982621 0.662553555
qout28/09/07_0828 Al 308.215 1.876946791 0.092218231
qout28/09/07_0828 Al 394.401 1.847608507 0.379860208
qout28/09/07_0828 Ca 317.933 25.26861179 0.208734716
qout28/09/07_0828 Ca 315.887 25.28583658 0.205881491
qout28/09/07_0828 Mg 285.213 3.061144372 0.161598605
qout28/09/07_0828 Mg 279.077 3.11840365 0.187383589
qout28/09/07_0828 Na 589.592 1.190355203 0.19956867
qout28/09/07_0828 Na 330.237 0.90281812 6.472679345
qout28/09/07_0828 Si 212.412 3.338019347 0.880325751
qout28/09/07_0828 Si 252.851 3.13282503 1.376495785
qout28/09/07_0828 Si 221.667 3.489392316 0.905446785
qout28/09/07_0828 Cd 228.802 -0.007821258 7.261802621
qout28/09/07_0828 Cd 214.440 -0.00964632 2.786657846
qout28/09/07_0828 Cu 327.393 0.007571486 16.63647438
qout28/09/07_0828 Cu 324.752 0.003936266 14.34227857
qout28/09/07_0828 Cu 224.700 -0.003458169 99.6126008
qout28/09/07_0828 K 766.490 0.568781069 0.289135699
qout28/09/07_0828 Fe 238.204 -0.004891065 13.34230762
qout28/09/07_0828 Fe 239.562 -0.000469743 119.1827309
qout28/09/07_0828 Fe 259.939 0.00062601 82.41228969
qout28/09/07_0828 Li 670.784 0.033838726 0.210646589
qout28/09/07_0828 Li 610.362 -0.023815826 2.760011309
qout28/09/07_0828 Li 460.286 -0.006005957 260.864681
qout28/09/07_0828 Mn 257.610 0.263598343 0.290769617
qout28/09/07_0828 Mn 260.568 0.251827308 1.297797721
qout28/09/07_0828 Ni 231.604 0.048899413 6.30964419
qout28/09/07_0828 Sr 407.771 0.08390571 0.24785032
qout28/09/07_0828 Sr 421.552 0.082778648 0.22913915
qout28/09/07_0828 Sr 460.733 0.100399504 0.189691509
qout28/09/07_0828 V 310.230 -0.055265142 3.866646258
qout28/09/07_0828 Zn 206.200 0.1222436 0.161488445
qout28/09/07_0828 Zn 213.857 0.130206812 0.59917466
qout28/09/07_0828 Ce 413.764 0.010061098 32.20858955
qout28/09/07_0828 Ce 418.660 -0.005487824 40.70275922
qout28/09/07_0828 Co 228.616 0.034688565 1.014763045
qout28/09/07_0828 Cr 267.716 -0.002511058 65.60513331
qout28/09/07_0828 Cr 205.560 -0.001232978 61.30478424
qout28/09/07_0828 Mo 202.031 -0.001591206 114.4113634
qout28/09/07_0828 Mo 203.845 -0.001786981 247.7386159
qout28/09/07_0828 Pb 220.353 -0.00425469 324.1271321
qout28/09/07_0828 Sn 235.485 -0.007321328 19.3557023
qout28/09/07_0828 U 385.958
qout28/09/07_0828 U 409.014
qout28/09/07_0828 Al 396 RAD 1.973069397 0.675175677
qout28/09/07_0828 Al 308 RAD 1.991536423 2.66288779
qout28/09/07_0828 Al 394 RAD 1.966751362 0.337960747
qout28/09/07_0828 Ca 317 RAD 24.72796247 1.044463417
qout28/09/07_0828 Ca 315 RAD 24.09190255 1.346461255
qout28/09/07_0828 Mg 285 RAD 3.049832924 0.758368399
qout28/09/07_0828 Mg 279 RAD 3.088511761 2.691704232
qout28/09/07_0828 Na 589 RAD 1.171322893 1.03857452
qout28/09/07_0828 Na 330 RAD 0.388422802 20.00910146
qout28/09/07_0828 Si 212 RAD 3.658939203 2.624551609
qout28/09/07_0828 Si 252 RAD 3.501769763 3.105941209
q328/09/07_1410 Al 396.153 -0.050209291 5.140902619
q328/09/07_1410 Al 308.215 -0.186047413 1.914810583
q328/09/07_1410 Al 394.401 -0.045164347 11.1818785
q328/09/07_1410 Ca 317.933 9.893051657 0.074926945
q328/09/07_1410 Ca 315.887 9.857634293 0.077624988
q328/09/07_1410 Mg 285.213 0.967572319 0.127718139
q328/09/07_1410 Mg 279.077 0.964284342 0.872781146
q328/09/07_1410 Na 589.592 1.443933401 0.387471995
q328/09/07_1410 Na 330.237 0.510324423 10.666611
q328/09/07_1410 Si 212.412 3.381567169 0.877402755
q328/09/07_1410 Si 252.851 3.18754367 0.973466659
q328/09/07_1410 Si 221.667 3.57569096 1.678976847
q328/09/07_1410 Cd 228.802 -0.009723715 5.599864477
q328/09/07_1410 Cd 214.440 -0.00957076 1.709953571
q328/09/07_1410 Cu 327.393 -0.007699578 14.06348833
q328/09/07_1410 Cu 324.752 -0.009978635 5.528832184
q328/09/07_1410 Cu 224.700 -0.017010671 16.47839774
q328/09/07_1410 K 766.490 0.461185502 0.59318133
q328/09/07_1410 Fe 238.204 -0.010654362 20.94817661
q328/09/07_1410 Fe 239.562 -0.005170892 6.770546579
q328/09/07_1410 Fe 259.939 -0.005048937 4.426798951
q328/09/07_1410 Li 670.784 0.023575348 0.18283076
q328/09/07_1410 Li 610.362 -0.002628877 69.40931586
q328/09/07_1410 Li 460.286 -0.035420422 147.3916895
q328/09/07_1410 Mn 257.610 -0.016246737 1.019944527
q328/09/07_1410 Mn 260.568 -0.007112418 1.201602396
q328/09/07_1410 Ni 231.604 -0.008017626 35.36608776
q328/09/07_1410 Sr 407.771 0.056814782 0.04811626
q328/09/07_1410 Sr 421.552 0.054499197 0.01288217
q328/09/07_1410 Sr 460.733 0.077756363 0.890010477
q328/09/07_1410 V 310.230 -0.065701367 3.063037489
q328/09/07_1410 Zn 206.200 -0.010771119 14.09422636
q328/09/07_1410 Zn 213.857 -0.002844875 10.68082649
q328/09/07_1410 Ce 413.764 0.010112586 30.61493785
q328/09/07_1410 Ce 418.660 -0.003488227 56.91869349
q328/09/07_1410 Co 228.616 -0.001889852 134.7110713
q328/09/07_1410 Cr 267.716 -0.00194775 65.85341143
q328/09/07_1410 Cr 205.560 -0.001232447 61.08431981
q328/09/07_1410 Mo 202.031 0.004666848 61.70273713
q328/09/07_1410 Mo 203.845 0.006141918 56.67563181
q328/09/07_1410 Pb 220.353 -0.004445634 276.0608175
q328/09/07_1410 Sn 235.485 -0.007190969 14.59778407
q328/09/07_1410 U 385.958
q328/09/07_1410 U 409.014
q328/09/07_1410 Al 396 RAD -0.121029425 7.827291087
q328/09/07_1410 Al 308 RAD -0.15476933 15.3264983
q328/09/07_1410 Al 394 RAD -0.109226879 12.86850446
q328/09/07_1410 Ca 317 RAD 9.631477225 0.539153442
q328/09/07_1410 Ca 315 RAD 9.465995174 0.463198032
q328/09/07_1410 Mg 285 RAD 0.990182079 0.213080333
q328/09/07_1410 Mg 279 RAD 0.963520325 3.702681615
q328/09/07_1410 Na 589 RAD 1.606928369 0.5604906
q328/09/07_1410 Na 330 RAD -0.05950776 941.9735192
q328/09/07_1410 Si 212 RAD 3.551023328 0.980045612
q328/09/07_1410 Si 252 RAD 3.472883094 1.180725673
valley2pislfeb508_1045 Al 396.153 -0.044001146 3.530079656
valley2pislfeb508_1045 Al 308.215 -0.182320574 1.58562586
valley2pislfeb508_1045 Al 394.401 -0.044066792 4.175692808
valley2pislfeb508_1045 Ca 317.933 17.53479457 0.781709231
valley2pislfeb508_1045 Ca 315.887 17.4777597 0.721548938
valley2pislfeb508_1045 Mg 285.213 2.36659298 0.692951203
valley2pislfeb508_1045 Mg 279.077 2.447907804 0.923764115
valley2pislfeb508_1045 Na 589.592 1.631367682 0.490593862
valley2pislfeb508_1045 Na 330.237 0.81926538 21.49753257
valley2pislfeb508_1045 Si 212.412 4.15529684 0.416971227
valley2pislfeb508_1045 Si 252.851 3.896764332 0.667596365
valley2pislfeb508_1045 Si 221.667 4.406118724 0.245107918
valley2pislfeb508_1045 Cd 228.802 -0.007959839 16.73047434
valley2pislfeb508_1045 Cd 214.440 -0.010069151 3.244958201
valley2pislfeb508_1045 Cu 327.393 -0.005609284 19.23964946
valley2pislfeb508_1045 Cu 324.752 -0.009774261 4.444612864
valley2pislfeb508_1045 Cu 224.700 -0.018861486 6.492465746
valley2pislfeb508_1045 K 766.490 0.514305628 0.678971706
valley2pislfeb508_1045 Fe 238.204 -0.008865833 9.383476297
valley2pislfeb508_1045 Fe 239.562 -0.004415178 8.593443399
valley2pislfeb508_1045 Fe 259.939 -0.002838171 6.003291722
valley2pislfeb508_1045 Li 670.784 0.023661832 0.012407039
valley2pislfeb508_1045 Li 610.362 -0.018846725 8.577545241
valley2pislfeb508_1045 Li 460.286 -0.014925761 58.47604915
valley2pislfeb508_1045 Mn 257.610 -0.011216737 0.743547921
valley2pislfeb508_1045 Mn 260.568 -0.002040726 3.968090764
valley2pislfeb508_1045 Ni 231.604 -0.009562894 9.924563703
valley2pislfeb508_1045 Sr 407.771 0.057964361 0.702838413
valley2pislfeb508_1045 Sr 421.552 0.055733364 0.81193942
valley2pislfeb508_1045 Sr 460.733 0.079725029 0.30816753
valley2pislfeb508_1045 V 310.230 -0.075809505 2.654906056
valley2pislfeb508_1045 Zn 206.200 0.060303712 10.31875359
valley2pislfeb508_1045 Zn 213.857 0.067489877 7.127393561
valley2pislfeb508_1045 Ce 413.764 0.005538551 8.521282331
valley2pislfeb508_1045 Ce 418.660 -0.005602496 35.25215008
valley2pislfeb508_1045 Co 228.616 -0.001570548 35.31918434
valley2pislfeb508_1045 Cr 267.716 -0.003394144 44.44489275
valley2pislfeb508_1045 Cr 205.560 -0.00195628 80.42174661
valley2pislfeb508_1045 Mo 202.031 0.005800439 45.73732619
valley2pislfeb508_1045 Mo 203.845 0.002128724 210.4632017
valley2pislfeb508_1045 Pb 220.353 -0.008447001 52.05440205
valley2pislfeb508_1045 Sn 235.485 -0.010084225 38.07381885
valley2pislfeb508_1045 U 385.958
valley2pislfeb508_1045 U 409.014
valley2pislfeb508_1045 Al 396 RAD -0.1155689 2.631488093
valley2pislfeb508_1045 Al 308 RAD -0.1702707 24.25654315
valley2pislfeb508_1045 Al 394 RAD -0.109616255 18.87869442
valley2pislfeb508_1045 Ca 317 RAD 17.53257375 0.591275436
valley2pislfeb508_1045 Ca 315 RAD 16.88499336 0.793428353
valley2pislfeb508_1045 Mg 285 RAD 2.387880005 1.056270266
valley2pislfeb508_1045 Mg 279 RAD 2.361620468 2.167685299
valley2pislfeb508_1045 Na 589 RAD 1.866861202 0.362868432
valley2pislfeb508_1045 Na 330 RAD 0.569703947 20.15259257
valley2pislfeb508_1045 Si 212 RAD 4.438142187 1.214390468
valley2pislfeb508_1045 Si 252 RAD 4.315691632 3.037182362

 

[skfortner]

I'm not sure how to post the after...
I am sadly very low tech. Thanks for your time Andrew.

 

[Andrew Poulsom]

Taking this data:

Sample ID Analyte Name Conc (Calib) RSD (Conc)
blanka Al 396.153 -0.061671764 3.565806827
blanka Al 308.215 -0.194249893 3.192473704
blanka Al 394.401 -0.049272798 9.68050951
blanka Ca 317.933 -0.587148639 0.134633141
blanka Ca 315.887 -0.620033468 0.262156516
blanka Mg 285.213 -0.205192727 0.195626939
blanka Mg 279.077 -0.072342327 7.089947069

what would you expect to see after? Where does column 2 begin and end?

 

[skfortner]

Column 2 would have a header of (Al 396.153), followed by its concentration (-0.061671764) and RSD (3.565806827). The next column would have a column header of (Al 308.215) followed by its concentration and RSD. After the first sample (blanka) has been entered in this fashion, I would like the next sample 10ppMA to (Al 396.153) value (11.22718383) to appear in column 2, followed by its RSD ( 0.135066732) and so on...

When I look at the data, I typically compare concentrations of one element between different sites.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Dim c As Long
    Dim Cell As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    Set ShNew = Worksheets.Add
    For Each Cell In Rng
        If Cell.Value <> Cell.Offset(-1).Value Then
            r = r + 3
            c = 2
            With ShNew
                .Cells(r - 2, 1).Value = Cell.Value
                .Cells(r - 1, 1).Value = "Conc"
                .Cells(r, 1).Value = "RSD"
            End With
        End If
        With ShNew
            .Cells(r - 2, c).Value = Cell.Offset(0, 1).Value
            .Cells(r - 1, c).Value = Cell.Offset(0, 2).Value
            .Cells(r, c).Value = Cell.Offset(0, 3).Value
        End With
        c = c + 1
    Next Cell
End Sub

 

[skfortner]

Thanks you

Thanks Andrew! That worked great! I only had to copy the names of the elements into the first line- but it saved me many hours!!!! Now, I can analyze data instead of pasting it!!!