Excel 2007 : Filtering worksheet 1 using worksheet 2 as criteria

**mattbro** · 07-10-2011, 01:08 PM

Hello there. I have two worksheets containing customer details-worksheet 1 contains current customers-column A containing unique numeric customer identifiers. Worksheet 2 contains a larger customer list, some of which are obsolete, but column A also contains the customer identifiers. I want to filter worksheet 2 so that customers that don't appear in worksheet 1 are removed. Does anyone have any ideas? Many thanks,
Mattbro

**JBeaucaire** · 07-10-2011, 07:34 PM

1) In an empty column on sheet 2 put this formula in the second row, then copy down the whole dataset:

=ISNUMBER(MATCH(A2, Sheet1!A:A, 0))

A series of TRUE/FALSE answers will appear

2) Highlight the new column and turn on the AUTOFILTER

3) Filter this column for FALSE

4) Highlight all the rows remaining visible and delete the rows (don't clear them, delete them)

5) Turn off the Autofilter and clear the new column of data... done.

**mattbro** · 07-13-2011, 12:35 PM

Yes! Job done. Thanks for your help.

Originally Posted by JBeaucaire

1) In an empty column on sheet 2 put this formula in the second row, then copy down the whole dataset:

=ISNUMBER(MATCH(A2, Sheet1!A:A, 0))

A series of TRUE/FALSE answers will appear

2) Highlight the new column and turn on the AUTOFILTER

3) Filter this column for FALSE

4) Highlight all the rows remaining visible and delete the rows (don't clear them, delete them)

5) Turn off the Autofilter and clear the new column of data... done.