Vlookup, Index, Match every instance.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M.A.Tyler - 29 May 2008 17:03 GMT

"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

Reply to this Message

Joel - 29 May 2008 17:45 GMT

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.

> "D" "BA"
> 1 Military Major 1
[quoted text clipped - 20 lines]
>
> M.A.Tyler

Reply to this Message

M.A.Tyler - 29 May 2008 19:19 GMT

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.

> 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
[quoted text clipped - 24 lines]
> >
> > M.A.Tyler

Reply to this Message

Joel - 29 May 2008 19:33 GMT

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

> I tried that, not much happened other than the copy and paste.
>
[quoted text clipped - 29 lines]
> > >
> > > M.A.Tyler

Reply to this Message

M.A.Tyler - 29 May 2008 19:52 GMT

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.

> "D" "BA"
> 1 Military Major 1
[quoted text clipped - 20 lines]
>
> M.A.Tyler

Reply to this Message

Joel - 29 May 2008 20:13 GMT

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

> Hey Joel,
> I understand the concept and it dose work, however what I really need is the
[quoted text clipped - 26 lines]
> >
> > M.A.Tyler

Reply to this Message

M.A.Tyler - 29 May 2008 21:12 GMT

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,
[quoted text clipped - 57 lines]
> > >
> > > M.A.Tyler

Reply to this Message