How do I extract data given a criteria from a data set into another tab?

[Robotrix]

I hope I can clearly explain my question here. I am seeking to extract only specific entries from a data collection of approximately 1500 rows given a specific criteria (Cell H3). I've provided a screen shot of what I"m looking at with some Mock data to help the process. To summarize: I want to be able to automatically pull Project#, section, tech, MOB, & KM within all rows from "Data Dump Sheet" that begin with "CM-GF-20", and have them appear in "Past Runs Sheet". The code "CM-GF-20" is dependent on cell H3 from "Past Runs Sheet". I'm pretty sure it involves a sub-array formula of some sorts, but I don't seem to be able to get it to work, any and all help is appreciated!



Past Runs Sheet

*	A	B	C	D	E	F	G	H	I	J	K
1	*	Line Specifications	*	*	Trap Sections	*	*	Corresponding Line Code	*	*	*
2	*	*	*	*	*	*	*	*	*	*	*
3	*	Line Number:	*	*	Launch:	Cromer	*	CM-GF-20	*	*	*
4	*	Diameter:	20	*	Receive:	Gretna	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*
7	Past Line Runs	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*
9	Line Code	Project #	Section	Tech	MOB	KM	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*	*

<tbody>
</tbody>

Data Dump Sheet

*	A	B	C	D	E	F
7	*	*	*	*	*	*
8	*	*	*	*	*	*
9	Line Code	Project #	Section	Tech	MOB	KM
10	CM-GF-20	101211	20B	ISN	19	283
11	WS-NN-12	103639	12A	CD	1	32.91
12	WNR-EB-12	103639	12C	CLP	20	1.06
13	KB-QU-20	103639	20B	CLP	18	352.63
14	NN-WNR-20	103639	20F	CLP	20	72.84
15	YP-KB-24	103639	24A	CLP	19	175.85
16	YP-KB-24	103639	24A	ICR	22	175.85
17	YP-KB-24	103639	24A	SDY	21	175.85
18	CM-GF-16	106292	16C	CLP	43	282.7
19	CM-GF-16	106292	16C	ISN	77	282.7
20	KB-QU-20	106292	20D	SDY	72	352.63
21	EP-YP-24	106292	24A	CLP	55	175.45
22	EP-YP-24	106292	24B	CDX	69	175.45
23	EP-YP-24	106292	24B	ICR	73	175.45
24	EP-YP-24	106292	24B	SDY	70	175.45
25	KB-QU-24	106292	24C	ICR	74	352.91
26	KB-QU-24	106292	24C	SDY	71	352.91
27	EP-YP-24	106292	24F	CDX	75	175.45
28	KB-QU-24	106292	24G	CDX	76	352.91
29	QU-CM-34	106292	34C	CDX	57	254.64
30	YP-KB-34	106292	34E	WMX	60	175.6
31	NW-RE-30	107646	30A	MF3	17	194
32	ML-CD-30	107646	30B	CLP	18	206.04
33	VG-PL-36	107649	36A	M3X	6	46.56
34	CR-CS-36	107649	36C	M3X	7	71

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Robotrix]

Just confirming that you are not interested in a macro approach this time?
If you are, is the "specific Tech" stored on one of the sheets somewhere? (Where?)

Peter,

I'm looking to avoid the macro approach in this instance. To make my query a little more straight forward, lets use the example from the multiple vlookups link that yourself and Aladin contributed to: http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

Lets assume that there is no "Code" section within the data, but we would still like to pull all lines with description of "15MM Sandstone" & QTY of "3,018". Is there a way to do that? I really thought using if(and within the array formula would work, but I'm clearly doing something wrong.

 

[Aladin Akyurek]

Hi Aladin,

I was wondering if this array approach is useable with multiple criteria? For instance, lets say I want all lines that possess the given code, but also possess a specific Tech. I've tried using IF(And( within the "Small(" section of the formula, but with no success.

Peter,

I'm looking to avoid the macro approach in this instance. To make my query a little more straight forward, lets use the example from the multiple vlookups link that yourself and Aladin contributed to: http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

Lets assume that there is no "Code" section within the data, but we would still like to pull all lines with description of "15MM Sandstone" & QTY of "3,018". Is there a way to do that? I really thought using if(and within the array formula would work, but I'm clearly doing something wrong.

Hard to see whether these two specs mean the same. Would you give an example of a given code and a specific tech? In fact, testing for multiple conditions should be possible. AND cannot do this by the way because it returns a scalar (single) result, not an array of results...

AND(X=x1,Y=y1) where X and Y are equally sized multi-cell ranges can be re-written as:

IF(X=x1,IF(Y=y1,...

 

[Robotrix]

Hey,

Sorry this took a little bit of time, I had to make up some mock data and set it up. But this is essentially what I'm looking at for my data. Basically I'm hoping to accomplish this following:

Pull all rows from the data_dump sheet that fit three criteria. These are:
1) Last Run (years) is less than or equal to the "Time Capture" criteria (in this ex. it is 10)
2) Tech is equal to the selected "Technology" criteria (in this ex. it is CDX)
3) Actual Margin is greater than or equal to "Margin" criteria (in this ex. it is 60)

As usual, thanks a ton!

Data_Dump

*	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*	*
3	*	Time Capture:	10	*	Technology:	CDX	*	Margin:	60	*	*
4	*	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*
9	Last Run (Years)	Sec No	Tech	Actual Margin	Run No	Report Qtr	Report Year	Base Curr	Lease Deal	Remove From Order Book	Project Plan
10	2.7	20B	ISN	72	1	Q4	2011	USD	*	N	Y
11	8.2	12A	CD	0	1	Q1	2006	USD	*	N	N
12	8.5	12C	CLP	0	1	Q4	2005	USD	*	N	N
13	8.6	20B	CLP	0	2	Q4	2005	USD	*	N	N
14	8.5	20F	CLP	0	1	Q4	2005	USD	*	N	N
15	8.5	24A	CLP	0	2	Q4	2005	USD	*	N	N
16	3.5	24A	ICR	68	1	Q1	2011	USD	*	N	Y
17	3.3	24A	SDY	91	1	Q1	2011	USD	*	N	Y
18	7.6	16C	CLP	0	1	Q4	2006	USD	*	N	N
19	2.7	16C	ISN	71	1	Q4	2011	USD	*	N	Y
20	3.3	20D	SDY	95	1	Q1	2011	USD	*	N	Y
21	7.5	24A	CLP	0	1	Q4	2006	USD	*	N	N
22	3.6	24B	CDX	93	1	Q4	2010	USD	*	N	N
23	3.4	24B	ICR	85	1	Q1	2011	USD	*	N	Y
24	3.3	24B	SDY	99	1	Q1	2011	USD	*	N	Y
25	3.5	24C	ICR	86	1	Q1	2011	USD	*	N	Y
26	3.3	24C	SDY	63	1	Q1	2011	USD	*	N	Y
27	3.2	24F	CDX	81	1	Q2	2011	USD	*	N	Y
28	3.2	24G	CDX	63	1	Q2	2011	USD	*	N	Y
29	7.6	34C	CDX	0	1	Q4	2006	USD	*	N	N
30	7.4	34E	WMX	0	1	Q1	2007	USD	*	N	N
31	6.4	30A	MF3	0	1	Q1	2008	USD	*	N	N
32	6.6	30B	CLP	0	1	Q1	2008	USD	*	N	N
33	3.6	36A	M3X	38	1	Q4	2010	USD	*	N	N
34	3.6	36C	M3X	0	1	Q4	2010	USD	*	N	N
35	4.3	36D	ICS	53	1	Q2	2010	USD	*	N	N
36	3.6	36D	M3X	79	1	Q4	2010	USD	*	N	N

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:104px;"><col style="width:95px;"><col style="width:64px;"><col style="width:64px;"><col style="width:108px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Data_dump, A:K, with the headers in row 9.

Sheet1 (the destination sheet)

A1: Time Capture
B1: 10

A2: Technology
B2: CDX

A3: Margin
B3: 60

Row 4, A4: Idx, B4:L4 replicates row 9 in Data_Dump

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

=IFERROR(SMALL(IF(Data_Dump!$A$10:$A$36>=$B$1,
  IF(Data_Dump!$C$9:$C$36=$B$2,IF(Data_Dump!$D$9:$D$36>=$B$3,
  ROW(Data_Dump!$A$10:$A$36)-ROW(Data_Dump!$A$10)+1))),ROWS($A$5:A5)),"")

B5, just enter, copy across, and down:

=IF($A5="","",INDEX(Data_Dump!$A$10:$A$36,$A5,MATCH(B$4,Data_Dump!$A$9:$K$9,0)))

 

[Robotrix]

Thank-you so much again for your help. For my understanding, the And( function only has the capability to search for singular entries, while the nested if( functions you provided with me make use of the array? I'm glad you pointed that out, will make things easier down the road!

 

[Aladin Akyurek]

Thank-you so much again for your help.

You are welcome.

For my understanding, the And( function only has the capability to search for singular entries, while the nested if( functions you provided with me make use of the array? I'm glad you pointed that out, will make things easier down the road!

AND like OR is capable of evaluating a multicell range (a multi-item reference). The evaluation must logically result in a single value: either TRUE or FALSE.

 

[Peter_SSs]

Just a hint for using Excel jeanie & avoiding all those asterisks in your screen shots. They occur when your preview or edit your post. If doing either of those things, it is best if you re-Paste your jeanie code. For example, if you preview your post it is usually very easy to re-Paste the code because it is likely still on your Clipboard. If I later edit one of my posts I would go back to my Excel sheet and re-generate the jeanie code. It only takes a moment and makes the screen shot much easier for other people to copy to their own worksheet and test without first having to clear all the asterisks.

 

[Robotrix]

Just a hint for using Excel jeanie & avoiding all those asterisks in your screen shots. They occur when your preview or edit your post. If doing either of those things, it is best if you re-Paste your jeanie code. For example, if you preview your post it is usually very easy to re-Paste the code because it is likely still on your Clipboard. If I later edit one of my posts I would go back to my Excel sheet and re-generate the jeanie code. It only takes a moment and makes the screen shot much easier for other people to copy to their own worksheet and test without first having to clear all the asterisks.

I thought that's what was happening! Thanks for the input and I"ll definitely keep it in mind moving forward .