Sorting/extracting data in Excel 2003

**WV Mike** · 09-04-2007, 01:32 PM

I have a SS which has a list of birds in column A.
In columns B, C, D, etc there is an X to indicate if the bird was seen or not for that year.

How can I extract a list of the species which was seen in a particular year?

I have attached a screen shot of part of the file for clarification.

Thanks,
-Mike

http://EpicRoadTrips.us/

**Ron Coderre** · 09-04-2007, 02:50 PM

Try this:

Select your data range

From the Excel Main Menu
<data><filter><autofilter>
Click on the dropdown for any year and select "non-blanks"
Only birds spotted in that year will display (the other rows will be hidden)

When done....Click on the dropdown and select "ALL" to redisplay the hidden rows.

Is that something you can work with?

If you have more complicated requirements, I have another (more complicated) method.

**WV Mike** · 09-04-2007, 03:32 PM

Originally Posted by Ron Coderre

Try this:

Select your data range

From the Excel Main Menu
<data><filter><autofilter>
Click on the dropdown for any year and select "non-blanks"
Only birds spotted in that year will display (the other rows will be hidden)

When done....Click on the dropdown and select "ALL" to redisplay the hidden rows.

Is that something you can work with?

If you have more complicated requirements, I have another (more complicated) method.

Yes! That works for me. Thanks for coming up with a solutiuon an non user could understand.

-Mike

http://epicroadtrips.us/

**WV Mike** · 09-04-2007, 03:52 PM

Woops!
Spoke to soon.
When I tried this same method on the next year (second column), it worked on the first column again, not the second.

-Mike

**Ron Coderre** · 09-04-2007, 06:47 PM

I'm guessing you filtered one year then tried to filter another year, too.

The AutoFilter method I posted, while easy to implement, is limited to only working on one year at a time for your purposes. When done filtering for one year, you need to <data><filter><show all> before filtering the next year.

You might want to "upgrade" to Advanced Filtering. See Debra Dalgleish's excellent instructions:
http://www.contextures.com/xladvfilter01.html

Post back if you have more questions.

**WV Mike** · 09-04-2007, 07:12 PM

Originally Posted by Ron Coderre

I'm guessing you filtered one year then tried to filter another year, too.

The AutoFilter method I posted, while easy to implement, is limited to only working on one year at a time for your purposes. When done filtering for one year, you need to <data><filter><show all> before filtering the next year.

You might want to "upgrade" to Advanced Filtering. See Debra Dalgleish's excellent instructions:
http://www.contextures.com/xladvfilter01.html

Post back if you have more questions.

I did filtering for one year, then <data><filter><show all> before filtering the next year. But it does the same thing - sort the preceding year.

A work-a-round: I can filter, copy the results, then close and not save.
When I reopen the file I choose then next year and so on.
Clunky, but it works and that's all that matters to me.

Thanks again,
-Mike
http://epicroadtrips.us/

**Ron Coderre** · 09-04-2007, 07:32 PM

Can you verify for me that when you set up the AutoFilter....ALL of the years have dropdowns?

If NO....then you're only selecting one column of data at a time, instead of the entire data area, including every year column.

If Yes...then I'm very confused.

**WV Mike** · 09-04-2007, 07:49 PM

Originally Posted by Ron Coderre

Can you verify for me that when you set up the AutoFilter....ALL of the years have dropdowns?

If NO....then you're only selecting one column of data at a time, instead of the entire data area, including every year column.

If Yes...then I'm very confused.

There is only one dropdown for all the years. Now I am beginning to see why I had the problem.

-Mike
http://epicroadtrips.us/