Extracting a subset of rows to a new sheet based on column values

[cwhaley]

I'm thinking that I may have to switch to a database program to do
this, but I want to regularly extract a subset of rows to a 2nd sheet
based on various column values in the 1st sheet.

It seems that this shouldn't be rocket science, so maybe I'm missing
something that someone can help me with.

First of all, I know that I can do a series of sorts on the columns in
the 1st sheet to find the subset I want, then cut-and-paste those over
to the 2nd sheet. However, my data (in the 1st sheet) change weekly
and this would be very tedious (I know because I'm doing that now).

I've also considered VLOOKUPs in the 2nd sheet, but I can't think of a
way to do that without having the same number of rows (overall) in the
2nd sheet, even if most of them would be blank. I'd still have to sort
to bring them together as contiguous rows.

If this sounds totally obscure, let me try to express it a different
way. I want the formulas in my 2nd sheet to scan through all of the
rows in the first sheet and find the ones that meet certain criteria.
Then I want the rows that qualify from there to appear in the 2nd
sheet as CONSECUTIVE rows. It's getting that "consecutive" part to
happen that's eluding me (unless I get back to sorting again).

Any thoughts?

 

[CyberTaz]

As a thought - without knowing anything more about the data - you may want
to have a look here at the topics Managing Data & Managing Lists:

http://www.microsoft.com/mac/products/excel2004/using.aspx?pid=usingexcel200
4#Managing%20Data

In particular the Advanced Filter will extract the subset based on criteria,
then the subset can be copied to another sheet.

There are a number of other ways, so check back for more replies.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[cwhaley]

As a thought - without knowing anything more about the data - you may want
to have a look here at the topics Managing Data & Managing Lists:

http://www.microsoft.com/mac/products/excel2004/using.aspx?pid=usinge...
4#Managing%20Data

In particular the Advanced Filter will extract the subset based on criteria,
then the subset can be copied to another sheet.

There are a number of other ways, so check back for more replies.

HTH |:>)
Bob Jones
[MVP] Office:Mac

Thanks. I'm always amazed at how quickly I get replies here... an
excellent community!

I've had a quick glance and my first impression is that the Advanced
Filter might work for me, especially if I can get the results into a
2nd sheet without the copy-and-paste. The Advanced Filter document
kind of says you can, but I'll have to give it a try.

Meanwhile, I've conceptually come up with another alternative that
might also work, even though it's not that elegant. My criteria are
fairly complex and the data are mostly numerical, not categorical. In
principle, I could add an extra column in the first sheet that
calculates for each row the degree to which my criteria are met (as a
number). Then I could add a column at the far left ranking those
values. In my 2nd sheet, I'd only have to put numbers from 1 to N,
then for the other columns, do a VLOOKUP using those 1 to N values. As
I said, it isn't elegant, but it might work too. I'm just not sure
what would happen with ties, and I won't know what N is in advance.
But if I was doing a "Top 20" list or something, that might be the way
to go.

Anyway, unless I get some other suggestions, I'll compare the Advanced
Filter with my kludgy ranking trick.

Thanks again!

 

[JE McGimpsey]

Anyway, unless I get some other suggestions, I'll compare the Advanced
Filter with my kludgy ranking trick.

Advanced Filter is probably the way to go.

You might also look at Pivot Tables.

 

[CyberTaz]

I've had a quick glance and my first impression is that the Advanced
Filter might work for me, especially if I can get the results into a
2nd sheet without the copy-and-paste. The Advanced Filter document
kind of says you can, but I'll have to give it a try.

Actually you *can*, but it isn't documented (as far as I can determine) on
how to go about it... Took me years to figger it out on my own

Create your Criteria Range first & enter your criteria. You have to start
with the destination sheet (the one you want matching records copied to) as
your active sheet, then launch the Adv. Filter, specify the Copy To option,
then set your List Range & Copy To Range. If you start with the source sheet
active & specify a Copy To location on a different sheet the feature will
throw up a message that "you can't do that".