Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
"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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
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" wrote: "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
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" wrote: 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" wrote: "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
did the autofilter show only the rows with 1 in them. The copy will copy
only the visible data. "M.A.Tyler" wrote: 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" wrote: 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" wrote: "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
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. "M.A.Tyler" wrote: "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
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" wrote: 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. "M.A.Tyler" wrote: "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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup, Index, Match every instance.
Thanks Joel!
"Joel" wrote: 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" wrote: 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. "M.A.Tyler" wrote: "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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Index,match, vlookup? | Excel Discussion (Misc queries) | |||
Need Help with Index and Match or Vlookup | Excel Worksheet Functions | |||
Vlookup or Index/Match | Excel Discussion (Misc queries) | |||
vlookup, match/index ???? | Excel Worksheet Functions |