Vlookup, Index, Match every instance.

[M.A.Tyler]

"D" "BA"
1 Military Major 1
2 Military Major 5
3 Military Major 1
4 Military Major 3
5 Military Major 8
6 Military Major 5
7 Military Major 3
8 Military Major 2
9 Military Major 7
10 Military Major 3

I need to "find", from a much longer list, Military Major,(sheet1 Column
"D") , these enteries are all grouped together, 10 or less. For every
instance column "BA" is 1, move the information contained in the corosponding
cell in sheet1, column "X" to Sheet2, column "B".

In the example above rows 1 & 3 qualify, would the information populated in
Sheet1 "X" 1 & 3 necessarily need to appear in Sheet2 B1 & B3 or could it
appear in B1 & B2?

Hope I've explained well enough, Thanks in advance.

M.A.Tyler

 

[Joel]

Use Autofilters in the Data Menu - Filters. Highlight column BA and select
the filter. then select 1 in the autofilter. then copy copy D and paste in
the new worksheet.

 

[M.A.Tyler]

I tried that, not much happened other than the copy and paste.

Was hoping there was a method available to have Index, Match or Vlookup
capture multiple instances of the 1 entry in column BA.

 

[Joel]

did the autofilter show only the rows with 1 in them. The copy will copy
only the visible data.

 

[M.A.Tyler]

Hey Joel,
I understand the concept and it dose work, however what I really need is the
information in column X that corosponds to each instance of 1 in column BA.
That said I could copy column X instead of D, but was hoping this could be a
bit more automated. Perhaps a macro to be used with the filtering would work.

 

[Joel]

You posted your request on the General help. Usually people who want macro
help would post request on Programming help. I gave you the genral solution,
not the macro solution.

Belwo are two macros. the first was one I recorded using the Macro Record
doing what you wanted. then I cleaned up the code a little. See below

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/29/2008 by Joel Warburg
''
Columns("BA:BA").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="1"
Columns("X:X").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
ActiveSheet.Paste
End Sub

Sub macro3()
With Sheets("Sheet1")
.Columns("BA:BA").AutoFilter
.Columns("BA:BA").AutoFilter Field:=1, Criteria1:="1"
.Columns("X:X").Copy _
Destination:=Sheets("Sheet2").Columns("B:B")
End With
End Sub

 

[M.A.Tyler]

Thanks Joel!

You posted your request on the General help. Usually people who want macro
help would post request on Programming help. I gave you the genral solution,
not the macro solution.

Belwo are two macros. the first was one I recorded using the Macro Record
doing what you wanted. then I cleaned up the code a little. See below

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/29/2008 by Joel Warburg
''
Columns("BA:BA").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="1"
Columns("X:X").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
ActiveSheet.Paste
End Sub

Sub macro3()
With Sheets("Sheet1")
.Columns("BA:BA").AutoFilter
.Columns("BA:BA").AutoFilter Field:=1, Criteria1:="1"
.Columns("X:X").Copy _
Destination:=Sheets("Sheet2").Columns("B:B")
End With
End Sub