Data Extraction from a single large table with formulae or VBA (VLOOKUP? MATCH? INDEX)

[seattletimebandit]

Hello all,


I've got some data in a table (346 rows, 13 columns). I need to pull specific data from the range of cells based on the left-most column/rows (SampleID) and the column headers (Chemical Name).


Truncated table:

SampleID

Date

Benzene

Ethylbenzene

m,p-Xylene

o-Xylene

Toluene

Gasoline

cis-1,2-DCE

PCE

trans-1,2-DCE

TCE

VC

Sample1

1/10/14

0.0252 U

0.308

0.0328

0.0252 U

0.0252 U

6.30 U

Sample2

1/10/14

0.0247

0.0488

0.0953

0.0331

0.0224 U

179

0.0209 U

0.0340

0.0209 U

0.0209 U

0.0021 U

Sample3

1/10/14

29.5

153

496

63.0

2.71

9,580

0.0177 U

0.0177 U

0.0177 U

0.0177 U

0.0018 U

Sample4

1/10/14

0.0451

0.118

0.146

0.0234 U

0.0234 U

233

0.0257 U

0.216

0.0257 U

0.0257 U

0.0026 U

Sample5

1/10/14

0.344

2.93

2.23

0.0608

0.0213 U

235

Sample6

1/10/14

0.0195 U

0.0292 U

0.0399

0.0214

0.0195 U

4.87 U

<tbody>
</tbody>

The output would be:

Sample1	1/10/14
Benzene	0.0252 U
Ethylbenzene	0.308
m,p-Xylene	0.0328
o-Xylene	0.0252 U
Toluene	0.0252 U

<tbody>
</tbody>

Sample2	1/10/14
Benzene	0.0247
Ethylbenzene	0.0488
m,p-Xylene	0.0953
o-Xylene	0.0331
Toluene	0.0224 U

<tbody>
</tbody>

Sample3	1/10/14
Benzene	29.5
Ethylbenzene	153
m,p-Xylene	496
o-Xylene	63.0
Toluene	2.71

<tbody>
</tbody>

etc.




The output tables will be used as an overlay on an AutoCad map of a contaminated site with each ouput table pointing (with and arrow) to a monitoring well where the sample was collected.


I thought I could do this with VLOOKUP, but I'm not finding it to be workable. It would be nice to have several output tables be created for each SampleID, and also changing up the Chemical Name, either by a drop-down box to "build" the output table, one SampleID at a time, or choosing X number of Sample ID's and generating as many tables as the user wants. Either in the same worksheet of a new worksheet.


Thanks!


stb
​

 

[seattletimebandit]

Sorry, the bust was in my head.

I didn't notice that it was changing the chemical names. It works perfectly, it skips blank cells and when it hits a value in the cell, it uses the correct header cell as the chemical name. Sweet!

 

[Aladin Akyurek]

Sorry, the bust was in my head.

I didn't notice that it was changing the chemical names. It works perfectly, it skips blank cells and when it hits a value in the cell, it uses the correct header cell as the chemical name. Sweet!

Happy to hear that. Thanks for providing feedback.