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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cwhaley - 29 Apr 2007 19:35 GMT

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?

Reply to this Message

CyberTaz - 29 Apr 2007 21:41 GMT

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

On 4/29/07 2:35 PM, in article
1177871708.697447.49700@c35g2000hsg.googlegroups.com, "cwhaley"
<cpwhaley@gmail.com> wrote:

> 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
[quoted text clipped - 21 lines]
>
> Any thoughts?

Reply to this Message

cwhaley - 30 Apr 2007 15:37 GMT

> 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:
[quoted text clipped - 10 lines]
> 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!

Reply to this Message

JE McGimpsey - 30 Apr 2007 16:05 GMT

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

Reply to this Message

CyberTaz - 30 Apr 2007 17:20 GMT

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

Signature

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

Reply to this Message

Mac Forum / Applications / Excel / April 2007

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