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:


[TABLE="class: grid, width: 1100"]
<tbody>[TR]
[TD]SampleID[/TD]
[TD]Date[/TD]
[TD]Benzene[/TD]
[TD]Ethylbenzene[/TD]
[TD]m,p-Xylene[/TD]
[TD]o-Xylene[/TD]
[TD]Toluene[/TD]
[TD]Gasoline[/TD]
[TD]cis-1,2-DCE[/TD]
[TD]PCE[/TD]
[TD]trans-1,2-DCE[/TD]
[TD]TCE[/TD]
[TD]VC[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD]1/10/14[/TD]
[TD]0.0252 U[/TD]
[TD]0.308[/TD]
[TD]0.0328[/TD]
[TD]0.0252 U[/TD]
[TD]0.0252 U[/TD]
[TD]6.30 U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD]1/10/14[/TD]
[TD]0.0247[/TD]
[TD]0.0488[/TD]
[TD]0.0953[/TD]
[TD]0.0331[/TD]
[TD]0.0224 U[/TD]
[TD]179[/TD]
[TD]0.0209 U[/TD]
[TD]0.0340[/TD]
[TD]0.0209 U[/TD]
[TD]0.0209 U[/TD]
[TD]0.0021 U[/TD]
[/TR]
[TR]
[TD]Sample3[/TD]
[TD]1/10/14[/TD]
[TD]29.5[/TD]
[TD]153[/TD]
[TD]496[/TD]
[TD]63.0[/TD]
[TD]2.71[/TD]
[TD]9,580[/TD]
[TD]0.0177 U[/TD]
[TD]0.0177 U[/TD]
[TD]0.0177 U[/TD]
[TD]0.0177 U[/TD]
[TD]0.0018 U[/TD]
[/TR]
[TR]
[TD]Sample4[/TD]
[TD]1/10/14[/TD]
[TD]0.0451[/TD]
[TD]0.118[/TD]
[TD]0.146[/TD]
[TD]0.0234 U[/TD]
[TD]0.0234 U[/TD]
[TD]233[/TD]
[TD]0.0257 U[/TD]
[TD]0.216[/TD]
[TD]0.0257 U[/TD]
[TD]0.0257 U[/TD]
[TD]0.0026 U[/TD]
[/TR]
[TR]
[TD]Sample5[/TD]
[TD]1/10/14[/TD]
[TD]0.344[/TD]
[TD]2.93[/TD]
[TD]2.23[/TD]
[TD]0.0608[/TD]
[TD]0.0213 U[/TD]
[TD]235[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample6[/TD]
[TD]1/10/14[/TD]
[TD]0.0195 U[/TD]
[TD]0.0292 U[/TD]
[TD]0.0399[/TD]
[TD]0.0214[/TD]
[TD]0.0195 U[/TD]
[TD]4.87 U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



The output would be:


[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Sample1[/TD]
[TD]1/10/14[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.0252 U[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.308[/TD]
[/TR]
[TR]
[TD]m,p-Xylene[/TD]
[TD]0.0328[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0252 U[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0252 U[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Sample2[/TD]
[TD]1/10/14[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]0.0247[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]0.0488[/TD]
[/TR]
[TR]
[TD]m,p-Xylene[/TD]
[TD]0.0953[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]0.0331[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]0.0224 U[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Sample3[/TD]
[TD]1/10/14[/TD]
[/TR]
[TR]
[TD]Benzene[/TD]
[TD]29.5[/TD]
[/TR]
[TR]
[TD]Ethylbenzene[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]m,p-Xylene[/TD]
[TD]496[/TD]
[/TR]
[TR]
[TD]o-Xylene[/TD]
[TD]63.0[/TD]
[/TR]
[TR]
[TD]Toluene[/TD]
[TD]2.71[/TD]
[/TR]
</tbody>[/TABLE]


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.