Filtering worksheet 1 using worksheet 2 as criteria

[mattbro]

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

[Haseeb Avarakkan]

Hello mattbro;

Assume this is you need, Filter Sheet2, If customers are not in Sheet1.

Say, your Sheet2 data is in A1 to down (including header). Insert 3 blank rows above the header. So the header should be in A4.

Then in A2 enter;

=ISNA(MATCH($A5,Sheet1!$A:$A,0))

($A5 is the first customer, Shet1!$A:$A is contains sheet1 customers)

you will TRUE or FALSE. Then select the data header to down, go to Data, sort & Filter group, click Advanced, or use keyboard shortcut Alt+A+Q

In the Advanced Filter window, select

List Range: Select header to last data
Criteria Range: select A1:A2

Click OK. You can see the customers are not in Sheet1.

If you want to copy the data instead of filtering, click Copy to another location, then down, select a range where you want to copy these customers in, Copy to:

HTH
Haseeb