Excel 2007: Filter one sheet based on a range of cells in another sheet

August 28, 2008 10:59 AM

Excel 2007: How do I filter one worksheet based on values in a range of cells in a second worksheet.

Example: ParentSheet has 50 rows.
ChildSheet has 36000 rows.

I want to filter ChildSheet to show only rows that have Column A = any of the values in ParentSheet, Column A.

I'd do this with a join or an In() in SQL, like: SELECT ChildSheet.* FROM ChildSheet WHERE ChidSheet.A = ParentSheet.A;

Is there an easy way to do this in Excel 2007?

posted by syzygy to Computers & Internet (5 answers total)

Best answer: Here's a quick hacky way:

Create a new column in ChildSheet.

In the new column, enter formula =COUNTIF(ParentSheet!$A$1:$A$50,A1).

Now you can autofilter based on the new column, excluding zero.
posted by Perplexity at 11:17 AM on August 28, 2008 [1 favorite]

Maybe not the easiest way, but what I would do is add a column to ChildSheet that does a vlookup of Column A on Column A of ParentSheet.

Example: There is some value in Cell A1 of ChildSheet. In Cell A2, you have "=vlookup(A1,ParentSheet!A:A,1,false)"

Then, you'll have whatever value is in A1 if it appears in ParentSheet, or #N/A if it doesn't. At that point, you can replace the #N/A's with 0, and the values with 1, and sort accordingly.
posted by milestogo at 11:20 AM on August 28, 2008

That would work too. Not sure which would be faster. Probably doesn't matter for this size sheet. Not sure you you can filter directly on "not #N/A"...
posted by Perplexity at 11:24 AM on August 28, 2008

Response by poster: Thanks already, but now I have another question. I'm trying to implement Perplexity's solution, but I'm having the following problem:

I type the formula into the first cell, works great. Now I copy it to the second cell...

Problem: The formula changes to: =COUNTIF(ParentSheet!$A$2:$A$51,A2)

So, the ParentSheet cell range is advancing (which I don't want), as is the ChildSheet criteria cell (which I do want).

Any way to make it so that the parent sheet range is always A1:A50, but the ChildSheet criteria cell increments to match the row the formula is on?
posted by syzygy at 11:34 AM on August 28, 2008

Response by poster: Sorry - I left the dollar signs out of my first formula. Adding them in did the trick...

Worked like a charm, thanks!
posted by syzygy at 11:37 AM on August 28, 2008