# Creating tabulated data from a different worksheet



## eagle923 (Tuesday at 3:19 PM)

Hi,  I'm a complete novice programmer and looking for some help with moving data from one worksheet to another.  

I have a "rawdata" sheet that comes out of an analysis report.  The number of samples and elements in the report changes every time.  I need a way to find each "Sample ID" in the rawdata, move it to columns in another sheet and then tabulate the " Measured intensities" into columns under each sample ID. 

Example file 2.xlsxABCDEFGH1Quantitative Analysis - Summary Report2Sample ID:Blank3Sample Date/Time:Thursday, July 03, 2014 14:44:544Sample Description:5Solution Type:Blank6Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.0027Number of Replicates:38Peak Processing Mode:Average9Signal Profile Processing Mode:Average10Dual Detector Mode:Dual11Dead Time (ns):5512Sample File:13Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth14Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00215Tuning File:C:\elandata_TERL Staff\Tuning\default.tun16Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac17Calibration File:18Calibration Type:External Calibration19Summary20Intensities21 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD22 Cu63363960.93623 Cu65171590.31824 Pt19435572.9625 Pt19536443.93226 Pt19627492.63327Concentration Results28 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit29 Cu63ppb30 Cu65ppb31 Pt194ppb32 Pt195ppb33 Pt196ppb34Quantitative Analysis - Summary Report35Sample ID:Standard 136Sample Date/Time:Thursday, July 03, 2014 14:46:4637Sample Description:38Solution Type:Standard39Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00240Number of Replicates:341Peak Processing Mode:Average42Signal Profile Processing Mode:Average43Dual Detector Mode:Dual44Dead Time (ns):5545Sample File:46Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth47Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 1.00348Tuning File:C:\elandata_TERL Staff\Tuning\default.tun49Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac50Calibration File:51Calibration Type:External Calibration52Summary53Intensities54 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD55 Cu631473410.64836395.710.93656 Cu65719040.15717158.510.31857 Pt1941164770.2473557.3632.9658 Pt1951206550.1133644.3983.93259 Pt196910490.832749.4162.63360Concentration Results61 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit62 Cu63110945.717.5260.21.1ppb63 Cu6554745.7317.6430.050.3ppb64 Pt194112920.122.3520.050.2ppb65 Pt195117010.322.4750.020.1ppb66 Pt19688299.6122.7780.160.7ppb67Quantitative Analysis - Summary Report68Sample ID:Standard 269Sample Date/Time:Thursday, July 03, 2014 14:48:2670Sample Description:71Solution Type:Standard72Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00273Number of Replicates:374Peak Processing Mode:Average75Signal Profile Processing Mode:Average76Dual Detector Mode:Dual77Dead Time (ns):5578Sample File:79Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth80Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 2.00481Tuning File:C:\elandata_TERL Staff\Tuning\default.tun82Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac83Calibration File:84Calibration Type:External Calibration85Summary86Intensities87 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD88 Cu633083260.41936395.710.93689 Cu651528510.32117158.510.31890 Pt1942951811.2823557.3632.9691 Pt1953046171.2743644.3983.93292 Pt1962296651.22749.4162.63393Concentration Results94 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit95 Cu63271930.451.1260.270.5ppb96 Cu65135692.151.1840.20.4ppb97 Pt194291623.852.8520.651.2ppb98 Pt195300972.952.8950.641.2ppb99 Pt196226915.452.9280.61.1ppb100Quantitative Analysis - Summary Report101Sample ID:Standard 3102Sample Date/Time:Thursday, July 03, 2014 14:50:01103Sample Description:104Solution Type:Standard105Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002106Number of Replicates:3107Peak Processing Mode:Average108Signal Profile Processing Mode:Average109Dual Detector Mode:Dual110Dead Time (ns):55111Sample File:112Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth113Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 3.005114Tuning File:C:\elandata_TERL Staff\Tuning\default.tun115Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac116Calibration File:117Calibration Type:External Calibration118Summary119Intensities120 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD121 Cu635557303.80936395.710.936122 Cu652766124.98717158.510.318123 Pt1945215498.7563557.3632.96124 Pt1955362878.7333644.3983.932125 Pt1964028918.3232749.4162.633126Concentration Results127 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit128 Cu63519334.5102.7644.424.3ppb129 Cu65259453.7102.4665.725.6ppb130 Pt194517991.891.4877.798.5ppb131 Pt195532642.791.2037.748.5ppb132 Pt196400141.390.6077.298.1ppb133Quantitative Analysis - Summary Report134Sample ID:Standard 4135Sample Date/Time:Thursday, July 03, 2014 14:51:44136Sample Description:137Solution Type:Standard138Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002139Number of Replicates:3140Peak Processing Mode:Average141Signal Profile Processing Mode:Average142Dual Detector Mode:Dual143Dead Time (ns):55144Sample File:145Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth146Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 4.006147Tuning File:C:\elandata_TERL Staff\Tuning\default.tun148Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac149Calibration File:150Calibration Type:External Calibration151Summary152Intensities153 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD154 Cu6310148150.09836395.710.936155 Cu655088760.19117158.510.318156 Pt19411767240.3933557.3632.96157 Pt19512149620.9923644.3983.932158 Pt1969227691.092749.4162.633159Concentration Results160 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit161 Cu63978419.8198.5840.210.1ppb162 Cu65491717.3198.7070.40.2ppb163 Pt1941173166203.3080.790.4ppb164 Pt1951211318203.4271.991ppb165 Pt196920020.1203.6872.191.1ppb166Quantitative Analysis - Summary Report167Sample ID:Std 2 A168Sample Date/Time:Thursday, July 03, 2014 14:54:21169Sample Description:170Solution Type:Sample171Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002172Number of Replicates:3173Peak Processing Mode:Average174Signal Profile Processing Mode:Average175Dual Detector Mode:Dual176Dead Time (ns):55177Sample File:178Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth179Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Std 2 A.007180Tuning File:C:\elandata_TERL Staff\Tuning\default.tun181Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac182Calibration File:183Calibration Type:External Calibration184Summary185Intensities186 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD187 Cu632784340.35236395.710.936188 Cu651366010.34417158.510.318189 Pt1943001470.8173557.3632.96190 Pt1953090790.623644.3983.932191 Pt1962315081.0452749.4162.633192Concentration Results193 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit194 Cu63242038.644.8870.20.5ppb195 Cu65119442.844.4510.190.4ppb196 Pt194296589.953.70.420.8ppb197 Pt195305434.453.6320.320.6ppb198 Pt196228758.553.3290.531ppbrawdata

Desired result table:
Example file 2.xlsxABCDEFGHI1I'd like to get the intensity data from "rawdata" for each sample into a table like the one below it, with referenced cells to "rawdata" (not Copy/Pasted from raw data)2with the "analytes and masses" down the rows (once per dataset is enough) and then each column be the "Sample ID" and 'Measured intensities" from each sample34Example Table:56AnalyteMassBlankStandard 1Standard 2Standard 3Standard 4Std 2 A7Cu633639614734130832655573010148152784348Cu6517159719041528512766125088761366019Pt1943557116477295181521549117672430014710Pt1953644120655304617536287121496230907911Pt196274991049229665402891922769231508intensCell FormulasRangeFormulaA6:B11,C7:C11A6=rawdata!B21C6C6=rawdata!B2D6D6=rawdata!B35E6E6=rawdata!B68F6F6=rawdata!B101G6G6=rawdata!B134H6H6=rawdata!B167D7:D11D7=rawdata!D55E7:E11E7=rawdata!D88F7:F11F7=rawdata!D121G7:G11G7=rawdata!D154H7:H11H7=rawdata!D187
There are two catches:
1) The number of elements can vary, so I need it to adjust to the number of elements.
2) I need the data as references back to "rawdata".  So if the Sample ID for sample 1 is in "rawdata cell B2" I need the new worksheet to be "=rawdata!B2" and not just the copied value.



I also have a very old (~1997) macro that does similar functionality that I can provide.  It does the searching and tabulating, but it only copies the data... it doesn't reference it.


----------



## Flashbond (Wednesday at 1:02 AM)

Quick 2 questions:

Are there always 2 Cu's and 3 Pt's?
Is data always in column B?


----------



## Flashbond (Wednesday at 2:48 AM)

I think this would be a resilient solution:

```
Sub testResults()
  Dim lRow As Long, x As Long, y As Long
  
  With Worksheets("rawdata")
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row - 1
    y = 3
    For i = 2 To lRow
      If .Cells(i, 1).Value = "Sample ID:" Then
        Worksheets("intens").Cells(6, y).Formula = "=" & .Name & "!" & .Cells(i, 2).Address
        For j = i + 1 To lRow
          If .Cells(j, 1).Value = "Intensities" Then
            x = 6
            For k = j + 1 To lRow
              If i = 2 Then
                Worksheets("intens").Cells(x, 1).Formula = "=" & .Name & "!" & .Cells(k, 2).Address
                Worksheets("intens").Cells(x, 2).Formula = "=" & .Name & "!" & .Cells(k, 3).Address
              End If
              If x > 6 Then
                Worksheets("intens").Cells(x, y).Formula = "=" & .Name & "!" & .Cells(k, 4).Address
              End If
              x = x + 1
              If .Cells(k + 1, 2).Value = "" Then Exit For
            Next
            Exit For
          End If
        Next
        i = k + 1
        y = y + 1
      End If
    Next
  End With
End Sub
```


----------



## eagle923 (Wednesday at 7:48 AM)

Thank you so much!  I think I can modify it from here and copy it enough to make it work for concentrations as well.  That is so much cleaner than the old macro that I was using (it must be ancient VBA code).


----------



## eagle923 (Wednesday at 7:50 AM)

Flashbond said:


> Quick 2 questions:
> 
> Are there always 2 Cu's and 3 Pt's?
> Is data always in column B?


No, it can change a lot from dataset to dataset (there may not even be Cu (copper) or Pt (platinum) in the next analysis.

The data format (columns that data is in) stays the same though.


----------



## Flashbond (Wednesday at 7:53 AM)

eagle923 said:


> No, it can change a lot from dataset to dataset (there may not even be Cu (copper) or Pt (platinum) in the next analysis.


Hmm this can be a problem wth my code. Can you please post a sample with such scenario and desired end result?


----------



## eagle923 (Wednesday at 8:14 AM)

Flashbond said:


> Hmm this can be a problem wth my code. Can you please post a sample with such scenario and desired end result?


Here is another dataset with different elements.  Your code seems to work OK though.  It produces a table on the "intens" page just fine.  I'll ask another question in a different reply so they aren't confusing though.
Example file 2 rawdata.xlsxABCDEFGH1Quantitative Analysis - Summary Report2Sample ID:Blank3Sample Date/Time:Monday, November 04, 2019 10:38:504Sample Description:5Solution Type:Blank6Blank File:C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.0017Number of Replicates:38Peak Processing Mode:Average9Signal Profile Processing Mode:Average10Dual Detector Mode:Dual11Dead Time (ns):5512Sample File:C:\Elandata\Sample\griffith Sr.sam13Method File:C:\Elandata\Method\TERL 2019\19-2066 Cheng REE.mth14Dataset File:C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.00115Tuning File:C:\Elandata\Tuning\Default.tun16Optimization File:C:\Elandata\Optimize\Default.dac17Calibration File:18Calibration Type:External Calibration19Summary20Intensities21 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD22|-Sc4563800.423| Y891024610.84124|>In1153269421.43625| La1396746.69226| Ce140104513.44927| Pr14119315.64328| Nd143497.11929| Nd1453313.80430| Sm1471534.31731| Sm1491115.74632| Eu15114915.59533| Eu15315513.23834| Gd1565019.09135| Gd1583710.32536| Gd1603721.45237| Tb1596811.10338| Dy1632513.85639| Dy1643116.76240| Ho165664.00941| Er1672422.04842| Er1682332.45143| Tm169812.544| Yb173728.57145| Yb174104046|-Lu1752321.49347Concentration Results48 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit49|-Sc45ng/mL50| Y89ng/mL51|>In115ng/mL52| La139ng/mL53| Ce140ng/mL54| Pr141ng/mL55| Nd143ng/mL56| Nd145ng/mL57| Sm147ng/mL58| Sm149ng/mL59| Eu151ng/mL60| Eu153ng/mL61| Gd156ng/mL62| Gd158ng/mL63| Gd160ng/mL64| Tb159ng/mL65| Dy163ng/mL66| Dy164ng/mL67| Ho165ng/mL68| Er167ng/mL69| Er168ng/mL70| Tm169ng/mL71| Yb173ng/mL72| Yb174ng/mL73|-Lu175ng/mL74Quantitative Analysis - Summary Report75Sample ID:Standard 176Sample Date/Time:Monday, November 04, 2019 10:42:0377Sample Description:78Solution Type:Standard79Blank File:C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.00180Number of Replicates:381Peak Processing Mode:Average82Signal Profile Processing Mode:Average83Dual Detector Mode:Dual84Dead Time (ns):5585Sample File:C:\Elandata\Sample\griffith Sr.sam86Method File:C:\Elandata\Method\TERL 2019\19-2066 Cheng REE.mth87Dataset File:C:\Elandata\Dataset\20-3013 Cheng REE 2\Standard 1.00288Tuning File:C:\Elandata\Tuning\Default.tun89Optimization File:C:\Elandata\Optimize\Default.dac90Calibration File:91Calibration Type:External Calibration92Summary93Intensities94 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD95|-Sc4575051.9466379.5720.496| Y89102573.70610246.1510.84197|>In1153287350.8493269421.43698| La13941703.099673.6926.69299| Ce14040511.7671044.72813.449100| Pr14143442.174192.66915.643101| Nd1435333.52449.3337.119102| Nd1453526.11632.66713.804103| Sm1475804.45314.66734.317104| Sm1495132.9241115.746105| Eu15119182.004149.33515.595106| Eu15321392.388155.00113.238107| Gd1568132.95250.33319.091108| Gd1588751.38636.66710.325109| Gd1608461.7413721.452110| Tb15935481.9676811.103111| Dy1638431.5562513.856112| Dy16410350.8713116.762113| Ho16532471.726664.009114| Er1677224.9422422.048115| Er1688403.63423.33332.451116| Tm16931881.043812.5117| Yb1735610.618728.571118| Yb17410831.8051040119|-Lu17533321.835232.0031.493120Concentration Results121 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit122|-Sc450.003ng/mL123| Y890ng/mL124|>In115328735.3ng/mL125| La1390.011ng/mL126| Ce1400.0090.101.5ng/mL127| Pr1410.013ng/mL128| Nd1430.001ng/mL129| Nd1450.001ng/mL130| Sm1470.002ng/mL131| Sm1490.002ng/mL132| Eu1510.005ng/mL133| Eu1530.006ng/mL134| Gd1560.002ng/mL135| Gd1580.003ng/mL136| Gd1600.002ng/mL137| Tb1590.011ng/mL138| Dy1630.002ng/mL139| Dy1640.003ng/mL140| Ho1650.01ng/mL141| Er1670.002ng/mL142| Er1680.002ng/mL143| Tm1690.01ng/mL144| Yb1730.002ng/mL145| Yb1740.003ng/mL146|-Lu1750.009ng/mL147Quantitative Analysis - Summary Report148Sample ID:Standard 2149Sample Date/Time:Monday, November 04, 2019 10:45:49150Sample Description:151Solution Type:Standard152Blank File:C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.001153Number of Replicates:3154Peak Processing Mode:Average155Signal Profile Processing Mode:Average156Dual Detector Mode:Dual157Dead Time (ns):55158Sample File:C:\Elandata\Sample\griffith Sr.sam159Method File:C:\Elandata\Method\TERL 2019\19-2066 Cheng REE.mth160Dataset File:C:\Elandata\Dataset\20-3013 Cheng REE 2\Standard 2.003161Tuning File:C:\Elandata\Tuning\Default.tun162Optimization File:C:\Elandata\Optimize\Default.dac163Calibration File:164Calibration Type:External Calibration165Summary166Intensities167 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD168|-Sc45208050.6196379.5720.4169| Y89397340.91710246.1510.841170|>In1153293790.933269421.436171| La139382520.331673.6926.692172| Ce140357980.7371044.72813.449173| Pr141420650.272192.66915.643174| Nd14350752.29149.3337.119175| Nd14533943.16532.66713.804176| Sm14760100.91314.66734.317177| Sm14953672.2411115.746178| Eu151184540.195149.33515.595179| Eu153205821.383155.00113.238180| Gd15677680.13850.33319.091181| Gd15886351.07636.66710.325182| Gd16082421.4283721.452183| Tb159355731.0136811.103184| Dy16384330.4022513.856185| Dy164105781.2273116.762186| Ho165328261.22664.009187| Er16773220.5582422.048188| Er16884970.38823.33332.451189| Tm169323660.84812.5190| Yb17355101.255728.571191| Yb174106061.2561040192|-Lu175325471.204232.0031.493193Concentration Results194 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit195|-Sc450.04410.011.2ng/mL196| Y890.089100.2ng/mL197|>In115329378.7ng/mL198| La1390.11410.011ng/mL199| Ce1400.1051.0210.011ng/mL200| Pr1410.12710.010.7ng/mL201| Nd1430.01510.021.6ng/mL202| Nd1450.0110.043.6ng/mL203| Sm1470.01810.021.7ng/mL204| Sm1490.01610.021.8ng/mL205| Eu1510.05610.010.9ng/mL206| Eu1530.06210.022.2ng/mL207| Gd1560.02310.010.9ng/mL208| Gd1580.02610.022ng/mL209| Gd1600.02510.010.6ng/mL210| Tb1590.10810.010.8ng/mL211| Dy1630.02610.010.8ng/mL212| Dy1640.03210.022.2ng/mL213| Ho1650.09910.022.1ng/mL214| Er1670.02210.011.3ng/mL215| Er1680.02610.010.7ng/mL216| Tm1690.09810.011.2ng/mL217| Yb1730.01710.010.6ng/mL218| Yb1740.03210.011ng/mL219|-Lu1750.09810.011.4ng/mLrawdata


----------



## Flashbond (Wednesday at 8:17 AM)

Ahh OK so, in the same rawdata are there always same elements? Then my code has that resiliency.
When you said "different datasheets", I thought there can be different elements per test in the same rawdata.


----------



## eagle923 (Wednesday at 8:21 AM)

A new question regarding the "Concentration Results" now.  So I tried to modify your code so it would produce the same table under "concs" sheet, but use the concentration results (which are in column E isntead of D where the intensities are).  It fills the table just fine except for the very last sample, it leaves off one result.

I assume this is because it's looking for a blank "" cell and stops early?

I changed the sheets to point to "concs" instead of "intens" and then line 19 I changed ".Cells(k, 4)" to ".Cells(k, 5)".
---Worksheets("conc").Cells(x, y).Formula = "=" & .Name & "!" & .Cells(k, 5).Address---

That now seems to make a concentration table, but when I run it, I end up with one missing value at the red square:


----------



## eagle923 (Wednesday at 8:23 AM)

Flashbond said:


> Ahh OK so, in the same rawdata are there always same elements? Then my code has that resiliency.
> When you said "different datasheets", I thought there can be different elements per test in the same rawdata.


Yes, the same "rawdata" will have the same elements.  If I run a new dataset, I always import it as "rawdata" and then the elements can change, but they won't vary within a dataset.


----------



## eagle923 (Tuesday at 3:19 PM)

Hi,  I'm a complete novice programmer and looking for some help with moving data from one worksheet to another.  

I have a "rawdata" sheet that comes out of an analysis report.  The number of samples and elements in the report changes every time.  I need a way to find each "Sample ID" in the rawdata, move it to columns in another sheet and then tabulate the " Measured intensities" into columns under each sample ID. 

Example file 2.xlsxABCDEFGH1Quantitative Analysis - Summary Report2Sample ID:Blank3Sample Date/Time:Thursday, July 03, 2014 14:44:544Sample Description:5Solution Type:Blank6Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.0027Number of Replicates:38Peak Processing Mode:Average9Signal Profile Processing Mode:Average10Dual Detector Mode:Dual11Dead Time (ns):5512Sample File:13Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth14Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00215Tuning File:C:\elandata_TERL Staff\Tuning\default.tun16Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac17Calibration File:18Calibration Type:External Calibration19Summary20Intensities21 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD22 Cu63363960.93623 Cu65171590.31824 Pt19435572.9625 Pt19536443.93226 Pt19627492.63327Concentration Results28 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit29 Cu63ppb30 Cu65ppb31 Pt194ppb32 Pt195ppb33 Pt196ppb34Quantitative Analysis - Summary Report35Sample ID:Standard 136Sample Date/Time:Thursday, July 03, 2014 14:46:4637Sample Description:38Solution Type:Standard39Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00240Number of Replicates:341Peak Processing Mode:Average42Signal Profile Processing Mode:Average43Dual Detector Mode:Dual44Dead Time (ns):5545Sample File:46Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth47Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 1.00348Tuning File:C:\elandata_TERL Staff\Tuning\default.tun49Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac50Calibration File:51Calibration Type:External Calibration52Summary53Intensities54 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD55 Cu631473410.64836395.710.93656 Cu65719040.15717158.510.31857 Pt1941164770.2473557.3632.9658 Pt1951206550.1133644.3983.93259 Pt196910490.832749.4162.63360Concentration Results61 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit62 Cu63110945.717.5260.21.1ppb63 Cu6554745.7317.6430.050.3ppb64 Pt194112920.122.3520.050.2ppb65 Pt195117010.322.4750.020.1ppb66 Pt19688299.6122.7780.160.7ppb67Quantitative Analysis - Summary Report68Sample ID:Standard 269Sample Date/Time:Thursday, July 03, 2014 14:48:2670Sample Description:71Solution Type:Standard72Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00273Number of Replicates:374Peak Processing Mode:Average75Signal Profile Processing Mode:Average76Dual Detector Mode:Dual77Dead Time (ns):5578Sample File:79Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth80Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 2.00481Tuning File:C:\elandata_TERL Staff\Tuning\default.tun82Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac83Calibration File:84Calibration Type:External Calibration85Summary86Intensities87 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD88 Cu633083260.41936395.710.93689 Cu651528510.32117158.510.31890 Pt1942951811.2823557.3632.9691 Pt1953046171.2743644.3983.93292 Pt1962296651.22749.4162.63393Concentration Results94 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit95 Cu63271930.451.1260.270.5ppb96 Cu65135692.151.1840.20.4ppb97 Pt194291623.852.8520.651.2ppb98 Pt195300972.952.8950.641.2ppb99 Pt196226915.452.9280.61.1ppb100Quantitative Analysis - Summary Report101Sample ID:Standard 3102Sample Date/Time:Thursday, July 03, 2014 14:50:01103Sample Description:104Solution Type:Standard105Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002106Number of Replicates:3107Peak Processing Mode:Average108Signal Profile Processing Mode:Average109Dual Detector Mode:Dual110Dead Time (ns):55111Sample File:112Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth113Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 3.005114Tuning File:C:\elandata_TERL Staff\Tuning\default.tun115Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac116Calibration File:117Calibration Type:External Calibration118Summary119Intensities120 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD121 Cu635557303.80936395.710.936122 Cu652766124.98717158.510.318123 Pt1945215498.7563557.3632.96124 Pt1955362878.7333644.3983.932125 Pt1964028918.3232749.4162.633126Concentration Results127 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit128 Cu63519334.5102.7644.424.3ppb129 Cu65259453.7102.4665.725.6ppb130 Pt194517991.891.4877.798.5ppb131 Pt195532642.791.2037.748.5ppb132 Pt196400141.390.6077.298.1ppb133Quantitative Analysis - Summary Report134Sample ID:Standard 4135Sample Date/Time:Thursday, July 03, 2014 14:51:44136Sample Description:137Solution Type:Standard138Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002139Number of Replicates:3140Peak Processing Mode:Average141Signal Profile Processing Mode:Average142Dual Detector Mode:Dual143Dead Time (ns):55144Sample File:145Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth146Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 4.006147Tuning File:C:\elandata_TERL Staff\Tuning\default.tun148Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac149Calibration File:150Calibration Type:External Calibration151Summary152Intensities153 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD154 Cu6310148150.09836395.710.936155 Cu655088760.19117158.510.318156 Pt19411767240.3933557.3632.96157 Pt19512149620.9923644.3983.932158 Pt1969227691.092749.4162.633159Concentration Results160 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit161 Cu63978419.8198.5840.210.1ppb162 Cu65491717.3198.7070.40.2ppb163 Pt1941173166203.3080.790.4ppb164 Pt1951211318203.4271.991ppb165 Pt196920020.1203.6872.191.1ppb166Quantitative Analysis - Summary Report167Sample ID:Std 2 A168Sample Date/Time:Thursday, July 03, 2014 14:54:21169Sample Description:170Solution Type:Sample171Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002172Number of Replicates:3173Peak Processing Mode:Average174Signal Profile Processing Mode:Average175Dual Detector Mode:Dual176Dead Time (ns):55177Sample File:178Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth179Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Std 2 A.007180Tuning File:C:\elandata_TERL Staff\Tuning\default.tun181Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac182Calibration File:183Calibration Type:External Calibration184Summary185Intensities186 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD187 Cu632784340.35236395.710.936188 Cu651366010.34417158.510.318189 Pt1943001470.8173557.3632.96190 Pt1953090790.623644.3983.932191 Pt1962315081.0452749.4162.633192Concentration Results193 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit194 Cu63242038.644.8870.20.5ppb195 Cu65119442.844.4510.190.4ppb196 Pt194296589.953.70.420.8ppb197 Pt195305434.453.6320.320.6ppb198 Pt196228758.553.3290.531ppbrawdata

Desired result table:
Example file 2.xlsxABCDEFGHI1I'd like to get the intensity data from "rawdata" for each sample into a table like the one below it, with referenced cells to "rawdata" (not Copy/Pasted from raw data)2with the "analytes and masses" down the rows (once per dataset is enough) and then each column be the "Sample ID" and 'Measured intensities" from each sample34Example Table:56AnalyteMassBlankStandard 1Standard 2Standard 3Standard 4Std 2 A7Cu633639614734130832655573010148152784348Cu6517159719041528512766125088761366019Pt1943557116477295181521549117672430014710Pt1953644120655304617536287121496230907911Pt196274991049229665402891922769231508intensCell FormulasRangeFormulaA6:B11,C7:C11A6=rawdata!B21C6C6=rawdata!B2D6D6=rawdata!B35E6E6=rawdata!B68F6F6=rawdata!B101G6G6=rawdata!B134H6H6=rawdata!B167D7:D11D7=rawdata!D55E7:E11E7=rawdata!D88F7:F11F7=rawdata!D121G7:G11G7=rawdata!D154H7:H11H7=rawdata!D187
There are two catches:
1) The number of elements can vary, so I need it to adjust to the number of elements.
2) I need the data as references back to "rawdata".  So if the Sample ID for sample 1 is in "rawdata cell B2" I need the new worksheet to be "=rawdata!B2" and not just the copied value.



I also have a very old (~1997) macro that does similar functionality that I can provide.  It does the searching and tabulating, but it only copies the data... it doesn't reference it.


----------



## eagle923 (Wednesday at 8:25 AM)

Here is the code that I tried to modify to also make a "conc" worksheet.  It runs fine until the very last sample, last element!

```
Sub testResultsconc()
  Dim lRow As Long, x As Long, y As Long
  
  With Worksheets("rawdata")
    lRow = .Cells(Rows.Count, 1).End(xlUp).Row - 1
    y = 3
    For i = 2 To lRow
      If .Cells(i, 1).Value = "Sample ID:" Then
        Worksheets("conc").Cells(6, y).Formula = "=" & .Name & "!" & .Cells(i, 2).Address
        For j = i + 1 To lRow
          If .Cells(j, 1).Value = "Concentration Results" Then
            x = 10
            For k = j + 1 To lRow
              If i = 2 Then
                Worksheets("conc").Cells(x, 1).Formula = "=" & .Name & "!" & .Cells(k, 2).Address
                Worksheets("conc").Cells(x, 2).Formula = "=" & .Name & "!" & .Cells(k, 3).Address
              End If
              If x > 10 Then
                Worksheets("conc").Cells(x, y).Formula = "=" & .Name & "!" & .Cells(k, 5).Address
              End If
              x = x + 1
              If .Cells(k + 1, 2).Value = "" Then Exit For
            Next
            Exit For
          End If
        Next
        i = k + 1
        y = y + 1
      End If
    Next
  End With
End Sub
```


----------



## Flashbond (Wednesday at 8:27 AM)

Are you sure that DSW1000 has a Lu value?
Weird. There is no reason to work for 3 samples but not for 10 samples.

I should see the rawdata. It may do this if Analyte column changes on the last record.


----------



## eagle923 (Wednesday at 8:32 AM)

Flashbond said:


> Are your values actually in Column E?


Yes, for the Concentration Results (which are listed after the Intensities in the "rawdata" sheet), the data I need is the "Conc. Mean" which is one more column over.

So intensites are in column D and concentrations are below those and one column further (column E)
Below is one sample again showing the sample name, intensity data and concentration data.
Example file 2.xlsxABCDEFGH34Quantitative Analysis - Summary Report35Sample ID:Standard 136Sample Date/Time:Thursday, July 03, 2014 14:46:4637Sample Description:38Solution Type:Standard39Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.00240Number of Replicates:341Peak Processing Mode:Average42Signal Profile Processing Mode:Average43Dual Detector Mode:Dual44Dead Time (ns):5545Sample File:46Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth47Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 1.00348Tuning File:C:\elandata_TERL Staff\Tuning\default.tun49Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac50Calibration File:51Calibration Type:External Calibration52Summary53Intensities54 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD55 Cu631473410.64836395.710.93656 Cu65719040.15717158.510.31857 Pt1941164770.2473557.3632.9658 Pt1951206550.1133644.3983.93259 Pt196910490.832749.4162.63360Concentration Results61 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit62 Cu63110945.717.5260.21.1ppb63 Cu6554745.7317.6430.050.3ppb64 Pt194112920.122.3520.050.2ppb65 Pt195117010.322.4750.020.1ppb66 Pt19688299.6122.7780.160.7ppbrawdata


----------



## eagle923 (Wednesday at 8:38 AM)

Flashbond said:


> Are you sure that DSW1000 has a Lu value?
> Weird. There is no reason to work for 3 samples but not for 10 samples.
> 
> I should see the rawdata. It may do this if Analyte column changes on the last record.


Yes, there is a value for Lu175 for DWS B1000.

If you use the original posts dataset (that has the 2 Cu and 3 Pt), make a worksheet named "conc"  and run the VBA code I posted for concs.  You'll see that it ends with 1 value missing.


----------



## Flashbond (Wednesday at 8:49 AM)

I think the fix is:

```
lRow = .Cells(Rows.Count, 2).End(xlUp).Row
```

Plus, you can change this to the 10th row:

```
Worksheets("conc").Cells(10, y).Formula = "=" & .Name & "!" & .Cells(i, 2).Address
```


----------



## eagle923 (Wednesday at 9:21 AM)

That works!  Is there a way to thank people on here or buy you coffee? 

On long datasets, this saves hours of work manually moving data since I can't just copy/paste.


----------



## Flashbond (Wednesday at 9:32 AM)

Glad it did work  I am already having my coffee. Thank you!
Instead, you can like my answer


----------



## eagle923 (Wednesday at 10:21 AM)

Ok new part to the same problem.  Now that moving the data works, I'm trying to automate importing the ".rep" file.  It is essentially a .csv file with a custom name from the instrument (". report"). 

If manally open excel, open the .rep file...it shows up as comma delimited.  If I then go to Data - Text to Columns and convert it as comma delimited... the macros work beautifully.
If I try the following code to import the file, it brings it in to what "looks" right but apparently is a hidden table.  Then the macro doesn't work.  Is there a better way to import this data?

```
Sub step2_Import_log()
'activates the rawdata worksheet to ensure the import goes into the correct worksheet
Worksheets("rawdata").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & GetFile, Destination:=Range( _
    "$A$1"))
    .Name = "logexportdata"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub


Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(FileFilter:="rep (*.rep), *.rep", Title:="Select File To Be Opened")
If filename__path = False Then Exit Function
GetFile = filename__path
End Function
```


----------



## Flashbond (Wednesday at 10:43 AM)

I am not experienced in importing files. I am not in front of the compter now. One example could be:

```
Sub OpenCsv()
  Workbooks.OpenText Filename:= "C:\csvtest\report.rep ",Local:=True
End Sub
```
You can adapt this to a file dialog.
The key is to set `Local=True` parameter according to this article:





						How to import csv files into Excel using VBA macros
					






					sitestory.dk


----------

