Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.)

share|improve this answer
    
thanks a lot! works fine –  j7891_10 Dec 14 '12 at 8:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.