I have a spreadsheet with a lot of rows. I want to filter this spreadsheet
and keep only the rows where the value in column X matches any of the values
in column Y of a different spreadsheet on another worksheet.
For example, if i have customer records including the town 'value' and
region is not one of the columns, I like to filter out the customers who live
in certain region. This region is defined in another worksheet as a
one-column spreadsheet just listing the towns in the region I'm interested
in. Don't worry about uniqueness of the relation between region and town.

How can I filter out the rows I need?

You can use a formula to indicate if that town is on the other worksheet.

=isnumber(match(a2,[book2.xls]sheet1!a:a,0))

Then filter to show the True's.

Chip Pearson has lots of techniques to work with duplicates:
http://www.cpearson.com/excel/duplicat.htm

mkrijger wrote:

I have a spreadsheet with a lot of rows. I want to filter this spreadsheet
and keep only the rows where the value in column X matches any of the values
in column Y of a different spreadsheet on another worksheet.
For example, if i have customer records including the town 'value' and
region is not one of the columns, I like to filter out the customers who live
in certain region. This region is defined in another worksheet as a
one-column spreadsheet just listing the towns in the region I'm interested
in. Don't worry about uniqueness of the relation between region and town.

How can I filter out the rows I need?

--

Dave Peterson