How to filter for results in TWO columns?

[redapplesonly]

Hi everyone,

I have spreadsheet that tracks flights between cities. The spreadsheet has become quite large, so I need a few simple tools or tricks to search it efficiently.

To keep things simple: the spreadsheet has "Departs" and "Arrives" columns, tracking the cities on either end of a flight. (See below)

Flight Departs Time Arrives Time
1001 New York 2:00 Boston 3:00
1002 New York 2:30 Philadelphia 3:15
1003 Boston 2:45 Philadelphia 4:00
1004 Washington DC 3:00 New York 4:00
1005 Philadelphia 3:30 Boston 5:00
1006 Boston 4:00 Washington DC 5:30

If my boss says, "give me a list of all flights going through New York," I have to manually filter for "New York" in the "Departs" column, copy that to another spreadsheet, then filter again for "New York" in the "Arrives" column, and manually glue the results together. In my little toy example, that's not a big deal, but when there are up to a couple hundred flights (and just as many cities), this gets tedious and error-prone.

Is there some handy way of filtering for "New York" in both the "Departs" and "Arrives" columns? Or some other way of achieving the same thing? ("Find All" isn't helpful unless I can export the full rows into a spreadsheet.)

Many thanks!
-RAO

[shg]

Add a column with a formula that catenates the origin and destination cities, and then filter that columns for 'contains'

If you want help with Excel, please post workbooks, not pictures.

[redapplesonly]

Hi, thanks for the fast reply. Sorry about the rookie posting, I'm still learning the ropes of the forum.

I'm also not sure what your formula would look like. I've attached a proper worksheet this time; can you possibly whip up a quick example so I can see what you mean?

Many thanks!
-ROA

[shg]

In G2 and copy down,

=C2 & "|" & E2

[FDibbins]

To pick up where shg left off (he is off-line at the moment), with the city you want in cell H2, use this in G2, copied down ...

=IF(OR(C2=$H$2,E2=$H$2),$H$2,"")

you can then just filter on that column

edit: ok so he isnt off-line now lol

But I dont think that formula will work, if Redapples wants to filter flights both to and from NY?

[shg]

Select the column filter, Text Filter, Contains, New York

[FDibbins]

ok yes, that works, i wasnt thinking about using "contains"