autofilter using array

dgrosen

Board Regular
Joined
May 3, 2003
Messages
108
I need help with the following.
I have 2 sheets. Sheet1 has an array of numbers (A1:J18). I need to use these to filter the data on sheet2 to show only those present on Sheet1
The number on sheet1 are on cells A1:J18 and those match the value on column B on sheet2.
Any help will be very much appreciated
Thanks
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

It would be easier if you could organise your array into a single column (A) then in a spare column on Sheet 2 enter :-

Code:
=--($B1=Sheet1!$A$1:$A$180)
with Control-Shift-Enter and copy down assuming that the first number is in Cell B1.

Then Filter for "1" in the spare column.

hth

Mike
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,271
Same concept as ukmikeb except the formula is not an array formula
=COUNTIF(Sheet1!$A$1:$J$18,B1)>0
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,996
Messages
5,514,692
Members
409,014
Latest member
evenyougreg

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top