Creating tabulated data from a different worksheet

[eagle923]

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
	A	B	C	D	E	F	G	H
1	Quantitative Analysis - Summary Report
2	Sample ID:	Blank
3	Sample Date/Time:	Thursday, July 03, 2014 14:44:54
4	Sample Description:
5	Solution Type:	Blank
6	Blank File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
7	Number of Replicates:	3
8	Peak Processing Mode:	Average
9	Signal Profile Processing Mode:	Average
10	Dual Detector Mode:	Dual
11	Dead Time (ns):	55
12	Sample File:
13	Method File:	C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
14	Dataset File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
15	Tuning File:	C:\elandata_TERL Staff\Tuning\default.tun
16	Optimization File:	C:\elandata_TERL Staff\Optimize\Default.dac
17	Calibration File:
18	Calibration Type:	External Calibration
19	Summary
20	Intensities
21		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
22		Cu	63	36396	0.936
23		Cu	65	17159	0.318
24		Pt	194	3557	2.96
25		Pt	195	3644	3.932
26		Pt	196	2749	2.633
27	Concentration Results
28		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
29		Cu	63					ppb
30		Cu	65					ppb
31		Pt	194					ppb
32		Pt	195					ppb
33		Pt	196					ppb
34	Quantitative Analysis - Summary Report
35	Sample ID:	Standard 1
36	Sample Date/Time:	Thursday, July 03, 2014 14:46:46
37	Sample Description:
38	Solution Type:	Standard
39	Blank File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
40	Number of Replicates:	3
41	Peak Processing Mode:	Average
42	Signal Profile Processing Mode:	Average
43	Dual Detector Mode:	Dual
44	Dead Time (ns):	55
45	Sample File:
46	Method File:	C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
47	Dataset File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 1.003
48	Tuning File:	C:\elandata_TERL Staff\Tuning\default.tun
49	Optimization File:	C:\elandata_TERL Staff\Optimize\Default.dac
50	Calibration File:
51	Calibration Type:	External Calibration
52	Summary
53	Intensities
54		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
55		Cu	63	147341	0.648	36395.71	0.936
56		Cu	65	71904	0.157	17158.51	0.318
57		Pt	194	116477	0.247	3557.363	2.96
58		Pt	195	120655	0.113	3644.398	3.932
59		Pt	196	91049	0.83	2749.416	2.633
60	Concentration Results
61		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
62		Cu	63	110945.7	17.526	0.2	1.1	ppb
63		Cu	65	54745.73	17.643	0.05	0.3	ppb
64		Pt	194	112920.1	22.352	0.05	0.2	ppb
65		Pt	195	117010.3	22.475	0.02	0.1	ppb
66		Pt	196	88299.61	22.778	0.16	0.7	ppb
67	Quantitative Analysis - Summary Report
68	Sample ID:	Standard 2
69	Sample Date/Time:	Thursday, July 03, 2014 14:48:26
70	Sample Description:
71	Solution Type:	Standard
72	Blank File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
73	Number of Replicates:	3
74	Peak Processing Mode:	Average
75	Signal Profile Processing Mode:	Average
76	Dual Detector Mode:	Dual
77	Dead Time (ns):	55
78	Sample File:
79	Method File:	C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
80	Dataset File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 2.004
81	Tuning File:	C:\elandata_TERL Staff\Tuning\default.tun
82	Optimization File:	C:\elandata_TERL Staff\Optimize\Default.dac
83	Calibration File:
84	Calibration Type:	External Calibration
85	Summary
86	Intensities
87		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
88		Cu	63	308326	0.419	36395.71	0.936
89		Cu	65	152851	0.321	17158.51	0.318
90		Pt	194	295181	1.282	3557.363	2.96
91		Pt	195	304617	1.274	3644.398	3.932
92		Pt	196	229665	1.2	2749.416	2.633
93	Concentration Results
94		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
95		Cu	63	271930.4	51.126	0.27	0.5	ppb
96		Cu	65	135692.1	51.184	0.2	0.4	ppb
97		Pt	194	291623.8	52.852	0.65	1.2	ppb
98		Pt	195	300972.9	52.895	0.64	1.2	ppb
99		Pt	196	226915.4	52.928	0.6	1.1	ppb
100	Quantitative Analysis - Summary Report
101	Sample ID:	Standard 3
102	Sample Date/Time:	Thursday, July 03, 2014 14:50:01
103	Sample Description:
104	Solution Type:	Standard
105	Blank File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
106	Number of Replicates:	3
107	Peak Processing Mode:	Average
108	Signal Profile Processing Mode:	Average
109	Dual Detector Mode:	Dual
110	Dead Time (ns):	55
111	Sample File:
112	Method File:	C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
113	Dataset File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 3.005
114	Tuning File:	C:\elandata_TERL Staff\Tuning\default.tun
115	Optimization File:	C:\elandata_TERL Staff\Optimize\Default.dac
116	Calibration File:
117	Calibration Type:	External Calibration
118	Summary
119	Intensities
120		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
121		Cu	63	555730	3.809	36395.71	0.936
122		Cu	65	276612	4.987	17158.51	0.318
123		Pt	194	521549	8.756	3557.363	2.96
124		Pt	195	536287	8.733	3644.398	3.932
125		Pt	196	402891	8.323	2749.416	2.633
126	Concentration Results
127		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
128		Cu	63	519334.5	102.764	4.42	4.3	ppb
129		Cu	65	259453.7	102.466	5.72	5.6	ppb
130		Pt	194	517991.8	91.487	7.79	8.5	ppb
131		Pt	195	532642.7	91.203	7.74	8.5	ppb
132		Pt	196	400141.3	90.607	7.29	8.1	ppb
133	Quantitative Analysis - Summary Report
134	Sample ID:	Standard 4
135	Sample Date/Time:	Thursday, July 03, 2014 14:51:44
136	Sample Description:
137	Solution Type:	Standard
138	Blank File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
139	Number of Replicates:	3
140	Peak Processing Mode:	Average
141	Signal Profile Processing Mode:	Average
142	Dual Detector Mode:	Dual
143	Dead Time (ns):	55
144	Sample File:
145	Method File:	C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
146	Dataset File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Standard 4.006
147	Tuning File:	C:\elandata_TERL Staff\Tuning\default.tun
148	Optimization File:	C:\elandata_TERL Staff\Optimize\Default.dac
149	Calibration File:
150	Calibration Type:	External Calibration
151	Summary
152	Intensities
153		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
154		Cu	63	1014815	0.098	36395.71	0.936
155		Cu	65	508876	0.191	17158.51	0.318
156		Pt	194	1176724	0.393	3557.363	2.96
157		Pt	195	1214962	0.992	3644.398	3.932
158		Pt	196	922769	1.09	2749.416	2.633
159	Concentration Results
160		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
161		Cu	63	978419.8	198.584	0.21	0.1	ppb
162		Cu	65	491717.3	198.707	0.4	0.2	ppb
163		Pt	194	1173166	203.308	0.79	0.4	ppb
164		Pt	195	1211318	203.427	1.99	1	ppb
165		Pt	196	920020.1	203.687	2.19	1.1	ppb
166	Quantitative Analysis - Summary Report
167	Sample ID:	Std 2 A
168	Sample Date/Time:	Thursday, July 03, 2014 14:54:21
169	Sample Description:
170	Solution Type:	Sample
171	Blank File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Blank.002
172	Number of Replicates:	3
173	Peak Processing Mode:	Average
174	Signal Profile Processing Mode:	Average
175	Dual Detector Mode:	Dual
176	Dead Time (ns):	55
177	Sample File:
178	Method File:	C:\elandata_TERL Staff\Method\TERL 2014\14-6035 Coleman PtCu\14-6035 Coleman PtCu.mth
179	Dataset File:	C:\elandata_TERL Staff\Dataset\14-6035 Coleman PtCu\Std 2 A.007
180	Tuning File:	C:\elandata_TERL Staff\Tuning\default.tun
181	Optimization File:	C:\elandata_TERL Staff\Optimize\Default.dac
182	Calibration File:
183	Calibration Type:	External Calibration
184	Summary
185	Intensities
186		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
187		Cu	63	278434	0.352	36395.71	0.936
188		Cu	65	136601	0.344	17158.51	0.318
189		Pt	194	300147	0.817	3557.363	2.96
190		Pt	195	309079	0.62	3644.398	3.932
191		Pt	196	231508	1.045	2749.416	2.633
192	Concentration Results
193		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
194		Cu	63	242038.6	44.887	0.2	0.5	ppb
195		Cu	65	119442.8	44.451	0.19	0.4	ppb
196		Pt	194	296589.9	53.7	0.42	0.8	ppb
197		Pt	195	305434.4	53.632	0.32	0.6	ppb
198		Pt	196	228758.5	53.329	0.53	1	ppb
rawdata

Desired result table:

Example file 2.xlsx
	A	B	C	D	E	F	G	H	I
1	I'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)
2	with 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
4	Example Table:
5
6	Analyte	Mass	Blank	Standard 1	Standard 2	Standard 3	Standard 4	Std 2 A
7	Cu	63	36396	147341	308326	555730	1014815	278434
8	Cu	65	17159	71904	152851	276612	508876	136601
9	Pt	194	3557	116477	295181	521549	1176724	300147
10	Pt	195	3644	120655	304617	536287	1214962	309079
11	Pt	196	2749	91049	229665	402891	922769	231508
intens

Cell Formulas
Range	Formula
A6:B11,C7:C11	A6	=rawdata!B21
C6	C6	=rawdata!B2
D6	D6	=rawdata!B35
E6	E6	=rawdata!B68
F6	F6	=rawdata!B101
G6	G6	=rawdata!B134
H6	H6	=rawdata!B167
D7:D11	D7	=rawdata!D55
E7:E11	E7	=rawdata!D88
F7:F11	F7	=rawdata!D121
G7:G11	G7	=rawdata!D154
H7:H11	H7	=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.

 

[Legacy 143009]

Quick 2 questions:

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

 

[Legacy 143009]

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]

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]

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.

 

[Legacy 143009]

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]

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.xlsx
	A	B	C	D	E	F	G	H
1	Quantitative Analysis - Summary Report
2	Sample ID:	Blank
3	Sample Date/Time:	Monday, November 04, 2019 10:38:50
4	Sample Description:
5	Solution Type:	Blank
6	Blank File:	C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.001
7	Number of Replicates:	3
8	Peak Processing Mode:	Average
9	Signal Profile Processing Mode:	Average
10	Dual Detector Mode:	Dual
11	Dead Time (ns):	55
12	Sample File:	C:\Elandata\Sample\griffith Sr.sam
13	Method File:	C:\Elandata\Method\TERL 2019\19-2066 Cheng REE.mth
14	Dataset File:	C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.001
15	Tuning File:	C:\Elandata\Tuning\Default.tun
16	Optimization File:	C:\Elandata\Optimize\Default.dac
17	Calibration File:
18	Calibration Type:	External Calibration
19	Summary
20	Intensities
21		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
22	|-	Sc	45	6380	0.4
23	|	Y	89	10246	10.841
24	|>	In	115	326942	1.436
25	|	La	139	674	6.692
26	|	Ce	140	1045	13.449
27	|	Pr	141	193	15.643
28	|	Nd	143	49	7.119
29	|	Nd	145	33	13.804
30	|	Sm	147	15	34.317
31	|	Sm	149	11	15.746
32	|	Eu	151	149	15.595
33	|	Eu	153	155	13.238
34	|	Gd	156	50	19.091
35	|	Gd	158	37	10.325
36	|	Gd	160	37	21.452
37	|	Tb	159	68	11.103
38	|	Dy	163	25	13.856
39	|	Dy	164	31	16.762
40	|	Ho	165	66	4.009
41	|	Er	167	24	22.048
42	|	Er	168	23	32.451
43	|	Tm	169	8	12.5
44	|	Yb	173	7	28.571
45	|	Yb	174	10	40
46	|-	Lu	175	232	1.493
47	Concentration Results
48		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
49	|-	Sc	45					ng/mL
50	|	Y	89					ng/mL
51	|>	In	115					ng/mL
52	|	La	139					ng/mL
53	|	Ce	140					ng/mL
54	|	Pr	141					ng/mL
55	|	Nd	143					ng/mL
56	|	Nd	145					ng/mL
57	|	Sm	147					ng/mL
58	|	Sm	149					ng/mL
59	|	Eu	151					ng/mL
60	|	Eu	153					ng/mL
61	|	Gd	156					ng/mL
62	|	Gd	158					ng/mL
63	|	Gd	160					ng/mL
64	|	Tb	159					ng/mL
65	|	Dy	163					ng/mL
66	|	Dy	164					ng/mL
67	|	Ho	165					ng/mL
68	|	Er	167					ng/mL
69	|	Er	168					ng/mL
70	|	Tm	169					ng/mL
71	|	Yb	173					ng/mL
72	|	Yb	174					ng/mL
73	|-	Lu	175					ng/mL
74	Quantitative Analysis - Summary Report
75	Sample ID:	Standard 1
76	Sample Date/Time:	Monday, November 04, 2019 10:42:03
77	Sample Description:
78	Solution Type:	Standard
79	Blank File:	C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.001
80	Number of Replicates:	3
81	Peak Processing Mode:	Average
82	Signal Profile Processing Mode:	Average
83	Dual Detector Mode:	Dual
84	Dead Time (ns):	55
85	Sample File:	C:\Elandata\Sample\griffith Sr.sam
86	Method File:	C:\Elandata\Method\TERL 2019\19-2066 Cheng REE.mth
87	Dataset File:	C:\Elandata\Dataset\20-3013 Cheng REE 2\Standard 1.002
88	Tuning File:	C:\Elandata\Tuning\Default.tun
89	Optimization File:	C:\Elandata\Optimize\Default.dac
90	Calibration File:
91	Calibration Type:	External Calibration
92	Summary
93	Intensities
94		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
95	|-	Sc	45	7505	1.946	6379.572	0.4
96	|	Y	89	10257	3.706	10246.15	10.841
97	|>	In	115	328735	0.849	326942	1.436
98	|	La	139	4170	3.099	673.692	6.692
99	|	Ce	140	4051	1.767	1044.728	13.449
100	|	Pr	141	4344	2.174	192.669	15.643
101	|	Nd	143	533	3.524	49.333	7.119
102	|	Nd	145	352	6.116	32.667	13.804
103	|	Sm	147	580	4.453	14.667	34.317
104	|	Sm	149	513	2.924	11	15.746
105	|	Eu	151	1918	2.004	149.335	15.595
106	|	Eu	153	2139	2.388	155.001	13.238
107	|	Gd	156	813	2.952	50.333	19.091
108	|	Gd	158	875	1.386	36.667	10.325
109	|	Gd	160	846	1.741	37	21.452
110	|	Tb	159	3548	1.967	68	11.103
111	|	Dy	163	843	1.556	25	13.856
112	|	Dy	164	1035	0.871	31	16.762
113	|	Ho	165	3247	1.726	66	4.009
114	|	Er	167	722	4.942	24	22.048
115	|	Er	168	840	3.634	23.333	32.451
116	|	Tm	169	3188	1.043	8	12.5
117	|	Yb	173	561	0.618	7	28.571
118	|	Yb	174	1083	1.805	10	40
119	|-	Lu	175	3332	1.835	232.003	1.493
120	Concentration Results
121		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
122	|-	Sc	45	0.003				ng/mL
123	|	Y	89	0				ng/mL
124	|>	In	115	328735.3				ng/mL
125	|	La	139	0.011				ng/mL
126	|	Ce	140	0.009	0.1	0	1.5	ng/mL
127	|	Pr	141	0.013				ng/mL
128	|	Nd	143	0.001				ng/mL
129	|	Nd	145	0.001				ng/mL
130	|	Sm	147	0.002				ng/mL
131	|	Sm	149	0.002				ng/mL
132	|	Eu	151	0.005				ng/mL
133	|	Eu	153	0.006				ng/mL
134	|	Gd	156	0.002				ng/mL
135	|	Gd	158	0.003				ng/mL
136	|	Gd	160	0.002				ng/mL
137	|	Tb	159	0.011				ng/mL
138	|	Dy	163	0.002				ng/mL
139	|	Dy	164	0.003				ng/mL
140	|	Ho	165	0.01				ng/mL
141	|	Er	167	0.002				ng/mL
142	|	Er	168	0.002				ng/mL
143	|	Tm	169	0.01				ng/mL
144	|	Yb	173	0.002				ng/mL
145	|	Yb	174	0.003				ng/mL
146	|-	Lu	175	0.009				ng/mL
147	Quantitative Analysis - Summary Report
148	Sample ID:	Standard 2
149	Sample Date/Time:	Monday, November 04, 2019 10:45:49
150	Sample Description:
151	Solution Type:	Standard
152	Blank File:	C:\Elandata\Dataset\20-3013 Cheng REE 2\Blank.001
153	Number of Replicates:	3
154	Peak Processing Mode:	Average
155	Signal Profile Processing Mode:	Average
156	Dual Detector Mode:	Dual
157	Dead Time (ns):	55
158	Sample File:	C:\Elandata\Sample\griffith Sr.sam
159	Method File:	C:\Elandata\Method\TERL 2019\19-2066 Cheng REE.mth
160	Dataset File:	C:\Elandata\Dataset\20-3013 Cheng REE 2\Standard 2.003
161	Tuning File:	C:\Elandata\Tuning\Default.tun
162	Optimization File:	C:\Elandata\Optimize\Default.dac
163	Calibration File:
164	Calibration Type:	External Calibration
165	Summary
166	Intensities
167		Analyte	Mass	Meas. Intens. Mean	Meas. Intens. RSD	Blank Intensity	Blank Intens. RSD
168	|-	Sc	45	20805	0.619	6379.572	0.4
169	|	Y	89	39734	0.917	10246.15	10.841
170	|>	In	115	329379	0.93	326942	1.436
171	|	La	139	38252	0.331	673.692	6.692
172	|	Ce	140	35798	0.737	1044.728	13.449
173	|	Pr	141	42065	0.272	192.669	15.643
174	|	Nd	143	5075	2.291	49.333	7.119
175	|	Nd	145	3394	3.165	32.667	13.804
176	|	Sm	147	6010	0.913	14.667	34.317
177	|	Sm	149	5367	2.241	11	15.746
178	|	Eu	151	18454	0.195	149.335	15.595
179	|	Eu	153	20582	1.383	155.001	13.238
180	|	Gd	156	7768	0.138	50.333	19.091
181	|	Gd	158	8635	1.076	36.667	10.325
182	|	Gd	160	8242	1.428	37	21.452
183	|	Tb	159	35573	1.013	68	11.103
184	|	Dy	163	8433	0.402	25	13.856
185	|	Dy	164	10578	1.227	31	16.762
186	|	Ho	165	32826	1.22	66	4.009
187	|	Er	167	7322	0.558	24	22.048
188	|	Er	168	8497	0.388	23.333	32.451
189	|	Tm	169	32366	0.84	8	12.5
190	|	Yb	173	5510	1.255	7	28.571
191	|	Yb	174	10606	1.256	10	40
192	|-	Lu	175	32547	1.204	232.003	1.493
193	Concentration Results
194		Analyte	Mass	Net Intens. Mean	Conc. Mean	Conc. SD	Conc. RSD	Sample Unit
195	|-	Sc	45	0.044	1	0.01	1.2	ng/mL
196	|	Y	89	0.089	1	0	0.2	ng/mL
197	|>	In	115	329378.7				ng/mL
198	|	La	139	0.114	1	0.01	1	ng/mL
199	|	Ce	140	0.105	1.021	0.01	1	ng/mL
200	|	Pr	141	0.127	1	0.01	0.7	ng/mL
201	|	Nd	143	0.015	1	0.02	1.6	ng/mL
202	|	Nd	145	0.01	1	0.04	3.6	ng/mL
203	|	Sm	147	0.018	1	0.02	1.7	ng/mL
204	|	Sm	149	0.016	1	0.02	1.8	ng/mL
205	|	Eu	151	0.056	1	0.01	0.9	ng/mL
206	|	Eu	153	0.062	1	0.02	2.2	ng/mL
207	|	Gd	156	0.023	1	0.01	0.9	ng/mL
208	|	Gd	158	0.026	1	0.02	2	ng/mL
209	|	Gd	160	0.025	1	0.01	0.6	ng/mL
210	|	Tb	159	0.108	1	0.01	0.8	ng/mL
211	|	Dy	163	0.026	1	0.01	0.8	ng/mL
212	|	Dy	164	0.032	1	0.02	2.2	ng/mL
213	|	Ho	165	0.099	1	0.02	2.1	ng/mL
214	|	Er	167	0.022	1	0.01	1.3	ng/mL
215	|	Er	168	0.026	1	0.01	0.7	ng/mL
216	|	Tm	169	0.098	1	0.01	1.2	ng/mL
217	|	Yb	173	0.017	1	0.01	0.6	ng/mL
218	|	Yb	174	0.032	1	0.01	1	ng/mL
219	|-	Lu	175	0.098	1	0.01	1.4	ng/mL
rawdata

 

[Legacy 143009]

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]

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]

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.