autofilter using array

[dgrosen]

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

 

[ukmikeb]

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 :-

=--($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

 

[AlphaFrog]

Same concept as ukmikeb except the formula is not an array formula
=COUNTIF(Sheet1!$A$1:$J$18,B1)>0