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

Robotrix · Jul 6, 2014

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
*	Line Specifications	*	*	Trap Sections	*	*	Corresponding Line Code	*	*	*
*	*	*	*	*	*	*	*	*	*	*
*	Line Number:	*	*	Launch:	Cromer	*	CM-GF-20	*	*	*
*	Diameter:	*	Receive:	Gretna	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*
Past Line Runs	*	*	*	*	*	*	*	*	*	*

Line Code	Project #	Section	Tech	MOB	KM	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

</tbody>

Data Dump Sheet

*	A	B	C	D	E	F
*	*	*	*	*	*

Line Code	Project #	Section	Tech	MOB	KM
CM-GF-20	20B	ISN
WS-NN-12	CD
WNR-EB-12	12C	CLP
KB-QU-20	20B	CLP
NN-WNR-20	20F	CLP
YP-KB-24	24A	CLP
YP-KB-24	24A	ICR
YP-KB-24	24A	SDY
CM-GF-16	16C	CLP
CM-GF-16	16C	ISN
KB-QU-20	20D	SDY
EP-YP-24	24A	CLP
EP-YP-24	24B	CDX
EP-YP-24	24B	ICR
EP-YP-24	24B	SDY
KB-QU-24	24C	ICR
KB-QU-24	24C	SDY
EP-YP-24	24F	CDX
KB-QU-24	24G	CDX
QU-CM-34	34C	CDX
YP-KB-34	34E	WMX
NW-RE-30	30A	MF3
ML-CD-30	30B	CLP
VG-PL-36	36A	M3X
CR-CS-36	36C	M3X

<tbody>
[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]
[TD="bgcolor: #333399"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]101211[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]283[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]103639[/TD]
[TD="align: right"]12A[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]32.91[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]103639[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]1.06[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]103639[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]352.63[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]103639[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]72.84[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]103639[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]175.85[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]103639[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"]175.85[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]103639[/TD]

[TD="align: right"]21[/TD]
[TD="align: right"]175.85[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"]282.7[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"]282.7[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]72[/TD]
[TD="align: right"]352.63[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"]175.45[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]69[/TD]
[TD="align: right"]175.45[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]73[/TD]
[TD="align: right"]175.45[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]175.45[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]74[/TD]
[TD="align: right"]352.91[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]71[/TD]
[TD="align: right"]352.91[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]175.45[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]76[/TD]
[TD="align: right"]352.91[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]57[/TD]
[TD="align: right"]254.64[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="align: right"]106292[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]175.6[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]

[TD="align: right"]107646[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]194[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]

[TD="align: right"]107646[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"]206.04[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]

[TD="align: right"]107649[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]46.56[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]

[TD="align: right"]107649[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]71[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Aladin Akyurek · Jul 6, 2014

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

Robotrix · Jul 6, 2014

Aladin Akyurek said:
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 · Jul 6, 2014

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

Rich (BB code):

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 · Jul 6, 2014

Peter_SSs said:

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

Rich (BB code):

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 · Jul 6, 2014

Robotrix said:
... 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.

Rich (BB code):

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 · Jul 7, 2014

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 · Jul 7, 2014

Robotrix said:
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 · Jul 12, 2014

Aladin Akyurek said:
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 · Jul 12, 2014

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

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

Robotrix

New Member

Aladin Akyurek

MrExcel MVP

Robotrix

New Member

Peter_SSs

MrExcel MVP, Moderator

Robotrix

New Member

Peter_SSs

MrExcel MVP, Moderator

Robotrix

New Member

Aladin Akyurek

MrExcel MVP

Robotrix

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Share this page

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

New Member

MrExcel MVP

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP

New Member

MrExcel MVP, Moderator

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock