Ahhhh okay, well my previous method won't work for that one!

Only way I can think of to do that is to add an extra column to your larger
list, incorporating a VLookUp of the value against the smaller list and then
filter on the #N/A errors.

"Stuart" wrote:

Many thanks for the answer - I don't think I explained myself well enough!

I have two lists - one has a series of values that I know exists in the
other but what I want to filter out are the values that are not in both lists.

to give you an idea of scale list one is 27000 rows and list 2 is about 4000
rows



"Dom_Ciccone" wrote:

Yes you can. Use an Advanced Filter to do this. There are certain rules you
need to follow to use an advanced filter.

1) You need to position the criteria in cells ABOVE the data list.
2) For multiple criteria on the same field you need to put each one in a
separate column

This might be easier to explain with an example:

A B C
1 Type Sales Sales
2 <Item1 <Item2
3
4 Type Sales
5 Sausages Item1
6 Sausages Item3

Your data range is in cells A4:B6. Use the Data--Filter--Advanced Filter
tool, where the list range is your data range (A4:B6) and the criteria range
is A1:C2.

By placing the not equal to (<) signs in the cells you force excel to
filter out those listed and by placing all the criteria on one row you make
it an AND statement. The above data would be filtered for those values that
were NOT Item1 AND were NOT Item2 (leaving just row 6).

Hope that helps.