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

 

[Aladin Akyurek]

Have a look at: http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

 

[Robotrix]

Have a look at: http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

I genuinely am not sure what I"m doing wrong. I tried the formula there, followed by CTRL+SHIFT+ENTER and for some reason I keep getting N/A error...

 

[Peter_SSs]

Would you consider a macro approach? If so, try this in a copy of your workbook.

Sub GetData()
  Application.ScreenUpdating = False
  Sheets("Past Runs Sheet").Range("A9").CurrentRegion.Offset(1).Resize(, 6).ClearContents
  With Sheets("Data Dump Sheet").Range("A9").CurrentRegion.Resize(, 6)
    .AutoFilter Field:=1, Criteria1:=Sheets("Past Runs Sheet").Range("H3").Value
    .Offset(1).Copy Destination:=Sheets("Past Runs Sheet").Range("A10")
    .AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub

 

[Robotrix]

Would you consider a macro approach? If so, try this in a copy of your workbook.

Sub GetData()
  Application.ScreenUpdating = False
  Sheets("Past Runs Sheet").Range("A9").CurrentRegion.Offset(1).Resize(, 6).ClearContents
  With Sheets("Data Dump Sheet").Range("A9").CurrentRegion.Resize(, 6)
    .AutoFilter Field:=1, Criteria1:=Sheets("Past Runs Sheet").Range("H3").Value
    .Offset(1).Copy Destination:=Sheets("Past Runs Sheet").Range("A10")
    .AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub

I would be entirely open to a macro approach. I tried this one in a copy of my workbook and it simply created a blue line in row 10, nothing else was changed, I ensured that all tab names were correct, etc.

 

[Peter_SSs]

... it simply created a blue line in row 10, nothing else was changed,....

I had assumed from your screen shots that row 8 on each sheet was blank. Perhaps that is not the case or there is further data to the right of what you have shown that caused my code to do that. In any case, try this version.

Sub GetData_v2()
  Dim wsDD As Worksheet, wsPR As Worksheet
  
  Set wsDD = Sheets("Data Dump Sheet")
  Set wsPR = Sheets("Past Runs Sheet")
  
  Application.ScreenUpdating = False
  With wsPR
    .Range("A9", .Range("A" & .Rows.Count).End(xlUp)).Offset(1).Resize(, 6).ClearContents
  End With
  With wsDD.Range("A9", wsDD.Range("A" & wsDD.Rows.Count).End(xlUp)).Resize(, 6)
      .AutoFilter Field:=1, Criteria1:=wsPR.Range("H3").Value
      .Offset(1).Copy Destination:=wsPR.Range("A10")
      .AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub

 

[Robotrix]

Thank-you both for your help. I managed to get the array formula to work, as well as the macro! I really do appreciate you taking the time to help out an excel dummy!

 

[Aladin Akyurek]

Thank-you both for your help. I managed to get the array formula to work, as well as the macro! I really do appreciate you taking the time to help out an excel dummy!

Great to hear that. Thanks.

 

[Robotrix]

Have a look at: http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

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_SSs]

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?)