In Excel, given a worksheet "A", how do you create a sheet "B" that has a subset of the rows in "A"?

Question

In Excel 2007, I have a sheet full of data "A". One of the columns in sheet "B" is called "Valid" and has either "yes" or "no".

I've created a second sheet "B". It's easy to make each row in "A" appear in "B" if the row is valid using an 'if' statement in each cell. But if it's invalid, there's a blank row.

I need "B" to show only the rows from "A" that are valid. TWO BIG CAVEATS: - No macros - No filtering (for long and complicated reasons).

I feel like it might be possible with vlookup used cleverly, but so far, I'm stumped.

Answer 1

I don't think you can use VLookup to do what you want because it's not really a "query" in an SQL sense. Excel doesn't have a query language.

You can use MS Query under Data|Get External Data|From Microsoft Query.

Select your XLSX file as the source, then under Options, you'll need to select System tables to see the columns you created. Then you can build a simple query to select what you need. If your file is going to move around a lot, then the only way this would work would be to split the source and destination worksheets into separate XLSX files.

Another alternative might be a pivot table, with your Validity column in the Report Filter and your column A in the Row labels. Then you can filter out the invalid rows in the Report Filter.