Creating tabulated data from a different worksheet

eagle923

New Member
Joined
Jan 10, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.xlsx
ABCDEFGH
1Quantitative Analysis - Summary Report
2Sample ID:Blank
3Sample Date/Time:Thursday, July 03, 2014 14:44:54
4Sample Description:
5Solution Type:Blank
6Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
7Number of Replicates:3
8Peak Processing Mode:Average
9Signal Profile Processing Mode:Average
10Dual Detector Mode:Dual
11Dead Time (ns):55
12Sample File:
13Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
14Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
15Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
16Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
17Calibration File:
18Calibration Type:External Calibration
19Summary
20Intensities
21 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
22 Cu63363960.936
23 Cu65171590.318
24 Pt19435572.96
25 Pt19536443.932
26 Pt19627492.633
27Concentration Results
28 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
29 Cu63ppb
30 Cu65ppb
31 Pt194ppb
32 Pt195ppb
33 Pt196ppb
34Quantitative Analysis - Summary Report
35Sample ID:Standard 1
36Sample Date/Time:Thursday, July 03, 2014 14:46:46
37Sample Description:
38Solution Type:Standard
39Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
40Number of Replicates:3
41Peak Processing Mode:Average
42Signal Profile Processing Mode:Average
43Dual Detector Mode:Dual
44Dead Time (ns):55
45Sample File:
46Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
47Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 1.003
48Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
49Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
50Calibration File:
51Calibration Type:External Calibration
52Summary
53Intensities
54 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
55 Cu631473410.64836395.710.936
56 Cu65719040.15717158.510.318
57 Pt1941164770.2473557.3632.96
58 Pt1951206550.1133644.3983.932
59 Pt196910490.832749.4162.633
60Concentration Results
61 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
62 Cu63110945.717.5260.21.1ppb
63 Cu6554745.7317.6430.050.3ppb
64 Pt194112920.122.3520.050.2ppb
65 Pt195117010.322.4750.020.1ppb
66 Pt19688299.6122.7780.160.7ppb
67Quantitative Analysis - Summary Report
68Sample ID:Standard 2
69Sample Date/Time:Thursday, July 03, 2014 14:48:26
70Sample Description:
71Solution Type:Standard
72Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
73Number of Replicates:3
74Peak Processing Mode:Average
75Signal Profile Processing Mode:Average
76Dual Detector Mode:Dual
77Dead Time (ns):55
78Sample File:
79Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
80Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 2.004
81Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
82Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
83Calibration File:
84Calibration Type:External Calibration
85Summary
86Intensities
87 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
88 Cu633083260.41936395.710.936
89 Cu651528510.32117158.510.318
90 Pt1942951811.2823557.3632.96
91 Pt1953046171.2743644.3983.932
92 Pt1962296651.22749.4162.633
93Concentration Results
94 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
95 Cu63271930.451.1260.270.5ppb
96 Cu65135692.151.1840.20.4ppb
97 Pt194291623.852.8520.651.2ppb
98 Pt195300972.952.8950.641.2ppb
99 Pt196226915.452.9280.61.1ppb
100Quantitative Analysis - Summary Report
101Sample ID:Standard 3
102Sample Date/Time:Thursday, July 03, 2014 14:50:01
103Sample Description:
104Solution Type:Standard
105Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
106Number of Replicates:3
107Peak Processing Mode:Average
108Signal Profile Processing Mode:Average
109Dual Detector Mode:Dual
110Dead Time (ns):55
111Sample File:
112Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
113Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 3.005
114Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
115Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
116Calibration File:
117Calibration Type:External Calibration
118Summary
119Intensities
120 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
121 Cu635557303.80936395.710.936
122 Cu652766124.98717158.510.318
123 Pt1945215498.7563557.3632.96
124 Pt1955362878.7333644.3983.932
125 Pt1964028918.3232749.4162.633
126Concentration Results
127 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
128 Cu63519334.5102.7644.424.3ppb
129 Cu65259453.7102.4665.725.6ppb
130 Pt194517991.891.4877.798.5ppb
131 Pt195532642.791.2037.748.5ppb
132 Pt196400141.390.6077.298.1ppb
133Quantitative Analysis - Summary Report
134Sample ID:Standard 4
135Sample Date/Time:Thursday, July 03, 2014 14:51:44
136Sample Description:
137Solution Type:Standard
138Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
139Number of Replicates:3
140Peak Processing Mode:Average
141Signal Profile Processing Mode:Average
142Dual Detector Mode:Dual
143Dead Time (ns):55
144Sample File:
145Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
146Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 4.006
147Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
148Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
149Calibration File:
150Calibration Type:External Calibration
151Summary
152Intensities
153 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
154 Cu6310148150.09836395.710.936
155 Cu655088760.19117158.510.318
156 Pt19411767240.3933557.3632.96
157 Pt19512149620.9923644.3983.932
158 Pt1969227691.092749.4162.633
159Concentration Results
160 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
161 Cu63978419.8198.5840.210.1ppb
162 Cu65491717.3198.7070.40.2ppb
163 Pt1941173166203.3080.790.4ppb
164 Pt1951211318203.4271.991ppb
165 Pt196920020.1203.6872.191.1ppb
166Quantitative Analysis - Summary Report
167Sample ID:Std 2 A
168Sample Date/Time:Thursday, July 03, 2014 14:54:21
169Sample Description:
170Solution Type:Sample
171Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
172Number of Replicates:3
173Peak Processing Mode:Average
174Signal Profile Processing Mode:Average
175Dual Detector Mode:Dual
176Dead Time (ns):55
177Sample File:
178Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
179Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Std 2 A.007
180Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
181Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
182Calibration File:
183Calibration Type:External Calibration
184Summary
185Intensities
186 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
187 Cu632784340.35236395.710.936
188 Cu651366010.34417158.510.318
189 Pt1943001470.8173557.3632.96
190 Pt1953090790.623644.3983.932
191 Pt1962315081.0452749.4162.633
192Concentration Results
193 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
194 Cu63242038.644.8870.20.5ppb
195 Cu65119442.844.4510.190.4ppb
196 Pt194296589.953.70.420.8ppb
197 Pt195305434.453.6320.320.6ppb
198 Pt196228758.553.3290.531ppb
rawdata


Desired result table:
Example file 2.xlsx
ABCDEFGHI
1I'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 sample
3
4Example Table:
5
6AnalyteMassBlankStandard 1Standard 2Standard 3Standard 4Std 2 A
7Cu63363961473413083265557301014815278434
8Cu651715971904152851276612508876136601
9Pt19435571164772951815215491176724300147
10Pt19536441206553046175362871214962309079
11Pt196274991049229665402891922769231508
intens
Cell Formulas
RangeFormula
A6:B11,C7:C11A6=rawdata!B21
C6C6=rawdata!B2
D6D6=rawdata!B35
E6E6=rawdata!B68
F6F6=rawdata!B101
G6G6=rawdata!B134
H6H6=rawdata!B167
D7:D11D7=rawdata!D55
E7:E11E7=rawdata!D88
F7:F11F7=rawdata!D121
G7:G11G7=rawdata!D154
H7: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.
 
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!
VBA Code:
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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.xlsx
ABCDEFGH
34Quantitative Analysis - Summary Report
35Sample ID:Standard 1
36Sample Date/Time:Thursday, July 03, 2014 14:46:46
37Sample Description:
38Solution Type:Standard
39Blank File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
40Number of Replicates:3
41Peak Processing Mode:Average
42Signal Profile Processing Mode:Average
43Dual Detector Mode:Dual
44Dead Time (ns):55
45Sample File:
46Method File:C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
47Dataset File:C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 1.003
48Tuning File:C:\elandata_TERL Staff\Tuning\default.tun
49Optimization File:C:\elandata_TERL Staff\Optimize\Default.dac
50Calibration File:
51Calibration Type:External Calibration
52Summary
53Intensities
54 AnalyteMassMeas. Intens. MeanMeas. Intens. RSDBlank IntensityBlank Intens. RSD
55 Cu631473410.64836395.710.936
56 Cu65719040.15717158.510.318
57 Pt1941164770.2473557.3632.96
58 Pt1951206550.1133644.3983.932
59 Pt196910490.832749.4162.633
60Concentration Results
61 AnalyteMassNet Intens. MeanConc. MeanConc. SDConc. RSDSample Unit
62 Cu63110945.717.5260.21.1ppb
63 Cu6554745.7317.6430.050.3ppb
64 Pt194112920.122.3520.050.2ppb
65 Pt195117010.322.4750.020.1ppb
66 Pt19688299.6122.7780.160.7ppb
rawdata
 
Upvote 0
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.
 
Upvote 0
I think the fix is:
VBA Code:
lRow = .Cells(Rows.Count, 2).End(xlUp).Row

Plus, you can change this to the 10th row:
VBA Code:
Worksheets("conc").Cells(10, y).Formula = "=" & .Name & "!" & .Cells(i, 2).Address
 
Upvote 0
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.
 
Upvote 0
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?
VBA Code:
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
 
Upvote 0
I am not experienced in importing files. I am not in front of the compter now. One example could be:
VBA Code:
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:
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top