Removing rows with a column mismatch compared against a list

Question

I spent some time googling this and couldn't find anything that I could modify to solve my problem.

I'm starting with a sheet consisting of 7 columns. In column C there is a five character reference. I have many rows. I also have a list of five character references in a separate sheet, let's say in column A.

I'd like to extract those rows whose five character references appear in the my list. I'd prefer to copy the 'good' rows to a third sheet, but I'd be ok with a solution that simply removes the 'bad' rows.

There is no nice order to either the list or the data.

I have been trying to use an advanced filter to do this. Here is my criteria:

=(ISNUMBER(MATCH(Sheet1!C:C,Sheet2!A:A,0)))

I then select the C column on Sheet 1 and the criteria in the Advanced Filter menu, but nothing happens.

What am I doing wrong?

Answer 1

Oh, you were sooo close!  I’m not sure you can use an Advanced Filter directly.  Instead, create a dummy column on Sheet1.  You can hide it — or maybe even delete it — when you’re done.  Let’s say D is the dummy column. Set D1 to

=ISNUMBER(MATCH(C1, Sheet2!A:A, 0))

and fill column D with it.  (Actually, I would use =NOT(ISNA(MATCH(C1, Sheet2!A:A, 0))), but they are pretty much equivalent in this context; it’s just a personal preference.)