How to filter a list based on another sheet?

[Delta223]

Hi;

I have an Excel file with a list of prospects, say car salesmen

I have another list with a list of zip codes I wish to target.

How do I use the list of zip codes (in a separate file) to go through the list of records in the prospect file and delete all the entries without a matching zip code?

I'm an Excel novice and the only way I can think of is copying over the target zip codes to the first file and running conditional formatting. A more ideal solution would just Hide or Delete the non-matching records (to save me the time of having to manually delete or hide records)

Thanks!

[Pete_UK]

You can insert a formula within the prospects sheet which examines each zip code and compares it with your list from the second file - something like this in row 2:

=IF(ISNA(MATCH(F2,'full_path[filename.xlsx]sheet_name'!A:A,0)),"delete","found")

assuming the zipcodes are in column F of the prospects file and in column A of the other file. Copy this down as far as required, then you can apply autofilter to this column to either hide or delete those records in one operation.

Hope this helps.

Pete

[benishiryo]

h Delta223. assuming your 1st file is Book1, with zip codes in B2:B10 and the other list is in Book2, with zip codes in B2:B10. try this in Book 1 C2:
=COUNTIF([Book2]Sheet1!$B$2:$B$10,B2)

then do an Autofilter. anything that is "0" does not exist in Book2. you can then delete or hide.

[Delta223]

Thanks guys! I'll mark this as solved now