Filtering excel sheets on multiple conditions

[Nerdhurd]

Howdy all, id like to an excel sheet and narrow it down to 11 cities in a coulmn which has over 500 different cities. Filter only allows me to do this for 2 cities max, is there another way i can filter this or am i stuck manually sorting and deleting the sheet? Thanks for any help

 

[SkipVought]

Hi,

Use Data/Advanced Filter with a Criteria for the 11 cities.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Nerdhurd]

Thanks! I tried advanced filter but I can't figure out how ot use the critera, ive looked in the help menu and it says format each condition like this

=''=text''

but i get an error when i try to do this ? Any ideas, thanks!

 

[SkipVought]

Typical Criteria for a Column with heading City with 4 criteria...
[tt]
City
Arlington
Dallas
Fort Worth
Hurst
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[KenWright]

As Skip has done I would suggest Adv Filter, but admittedly it is different to Autofilter, and not as intuitive. If you prefer the Autofilter method then one option would be to simply create your list of 11 cities, add another column to your data, and then use the COUNTIF function to compare the city in each record against the list of cities. You'll end up with a series of 1s and 0s and you just filter normally on the 1s.

By the by I know, but you can use the same method to create your own logical groupings in the source data for a Pivot table, be sure to pull that extra column(s) into the Pivot table, and give yourself more flexibility wrt what you can filter on.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[01Patrik]

If you only require further documentation about Adv filter, take a look at the built-in help on DSUM / DCOUNT - the criteria works the same way.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[Nerdhurd]

Thanks for all the help guys, Im just have a problem getting the syntax correct...still cant get it to work. For the criteria range in advanced filter im trying:

="=Arlington"

Which doesn't work, ive tried all sorts of variets...it wont take it. My list range is the whole spread sheet

$1:$65536

I also tried using using countif and autofilter. Seems like a great idea although COUNTIF doesnt allow you to use an OR so im kinda stuck sense I would need it to look at multiple cities

thanks again!

 

[gizmo1973]

http://www.digdb.com/

This is an excel add-in (it's not free though there is a trial version)
It gives many more autofilters than excel (not sure how many thought did get it upt to 20 as a test)as well as many other cool gizmos (pun intended)
Maybe this will help?

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[xlbo]

I think it might well be the list range that's doing it then - you should expect to use something like

$A$1:$Z$1000

The way you have it, you are defining a set of rows, rather than a range

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

still cant get it to work. For the criteria range in advanced filter im trying:

="=Arlington"

OF COURSE it doesn't work! Did you not read my previous post (28 Mar 05 20:31)??? Did you see an [Equal] sign in that list???

If those 4 cities along with the heading is in Z1:Z5, then your Criteria Range is Z1:Z5

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}