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
​

 

[KB_Miner]

Have you tried building a pivot table with this? Put the sample ID as your report filter, your chemical columns as the rows, and the date as the column.

 

[seattletimebandit]

KB,

I did try, but I'm not well versed in using them. However, with your guidance as to what goes where, I'll give it a go. Thanks!

 

[KB_Miner]

I tried playing around with it. Here's what I found worked best.
SampleID in the Report Filter
Date in the Column Labels
Your chemicals in the Values Section. When you do this, you'll need to do a couple things. First, click on the drop down and select Value Field Settings. Change the Summarize value field to Sum, then change the Custom Name to the chemical with a space after the name (so Excel doesn't give you an error). In the Column Labels section should now be a Values selection under Date. Click and drag that over to Row Labels.

I'd recommend pulling out any labels after the data in the table (like the U's). Pivot tables aren't fans of text values.

 

[seattletimebandit]

KB,

That worked as far as the format goes, but the data is all zero's.

SampleID	Sample1
	Date
Data	1/10/14	Grand Total
Benzene	0	0
Ethylbenzene	0	0
Toluene	0	0
m,p-Xylene	0	0
o-Xylene	0	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Sheet1, A:M, houses the source data with headers in the first row.

Sheet2

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

<tbody>
</tbody>

A1: Sample1

B1, just enter:

=VLOOKUP(A1,Sheet1!$A$2:$B$7,2,0)

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$C$1:$M$1,SMALL(IF(1-(INDEX(Sheet1!$C$2:$M$7,  MATCH($A$1,Sheet1!$A$2:$A$7,0),0)=""),  COLUMN(Sheet1!$C$1:$M$1)-COLUMN(Sheet1!$C$1)+1),ROWS($A$2:A2))),"")

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$C$2:$M$7,
  MATCH($A$1,Sheet1!$A$2:$A$7,0),
  SMALL(IF(1-(INDEX(Sheet1!$C$2:$M$7,
  MATCH($A$1,Sheet1!$A$2:$A$7,0),0)=""),
  COLUMN(Sheet1!$C$1:$M$1)-COLUMN(Sheet1!$C$1)+1),
  ROWS($A$2:A2))),"")

 

[seattletimebandit]

Hey Aladin,

I entered the formulas two the correct cells on sheet 2, hit CSE, and I can see the formulas with braces, but no data. For the VLOOKUP formula I get #NA (Value not available error).

Thoughts?

stb

 

[Aladin Akyurek]

Hey Aladin,

I entered the formulas two the correct cells on sheet 2, hit CSE, and I can see the formulas with braces, but no data. For the VLOOKUP formula I get #NA (Value not available error).

Thoughts?

stb

There was some IE trouble while replying. Not sure whether the formulas stayed intact thru multiple edits. Hence recap below...

Sheet1, A:M, with headers in row1.

Sheet2

A1: Sample1 (a sample of interest)

B1, just enter:

=VLOOKUP(A1,Sheet1!$A$2:$B$7,2,0)

A6, control+shift+enter and copy down:

=IFERROR(INDEX(Sheet1!$C$1:$M$1,
  SMALL(IF(1-(INDEX(Sheet1!$C$2:$M$7,MATCH($A$1,Sheet1!$A$2:$A$7,0),0)=""),
  COLUMN(Sheet1!$C$1:$M$1)-COLUMN(Sheet1!$C$1)+1),ROWS($A$2:A2))),"")

B6, control+shift+enter and copy down:

=IFERROR(INDEX(Sheet1!$C$2:$M$7,MATCH($A$1,Sheet1!$A$2:$A$7,0),
  SMALL(IF(1-(INDEX(Sheet1!$C$2:$M$7,MATCH($A$1,Sheet1!$A$2:$A$7,0),0)=""),
  COLUMN(Sheet1!$C$1:$M$1)-COLUMN(Sheet1!$C$1)+1),ROWS($A$2:A2))),"")

Here is the workbook that implements the set up described above:
https://dl.dropboxusercontent.com/u/65698317/seattletimebandit%20data%20extraction.xlsx

 

[seattletimebandit]

Thanks Aladin. I got it to work after downloading your spreadsheet. I actually have 347 rows to column Q, so I changed the cell refs in your formula. I created a Data Validation list to choose the SampleIDs which works well (instead of copying/pasting into Cell A1). The one bust if found was that some Sample ID's weren't analyzed for BTEX, so the cells are blank, however, when I choose a SampleID that does not have data in the BTEX cells, the formula skips those blanks and calls the cells that do have data (i.e., data for Gasoline, Oil, and other compounds populate my little table that contains the formula).

Is there a way for the formula to reference the blank cells and not skip over them (somekind of IsNULL function)?

stb

 

[Aladin Akyurek]

Thanks Aladin. I got it to work after downloading your spreadsheet. I actually have 347 rows to column Q, so I changed the cell refs in your formula. I created a Data Validation list to choose the SampleIDs which works well (instead of copying/pasting into Cell A1). The one bust if found was that some Sample ID's weren't analyzed for BTEX, so the cells are blank, however, when I choose a SampleID that does not have data in the BTEX cells, the formula skips those blanks and calls the cells that do have data (i.e., data for Gasoline, Oil, and other compounds populate my little table that contains the formula).

Is there a way for the formula to reference the blank cells and not skip over them (somekind of IsNULL function)?

stb

Would you illustrate the issue referring to the source data of which we have n exhibit in post #1?