Help me tame the Advanced Filters!

March 20, 2008 5:04 PM

Excel filter-filter: How do I set up an easily adjustable filter for a 7000-odd row spreadsheet in Excel Mac 2004 or 2008? The data's not well organised and needs to be filtered over multiple columns.

So we've got this huge-ass spreadsheet with over 7000 rows. Several of the columns contain cells that each have multiple words. It's a bit like this, but much bigger in both directions. (excuse the formatting):

No | Keywords 1 | Keywords 2
1 | brazil hat | cheese potato
2 | lemon fig | washboard hat
3 | cheese fig | brazil cheese

So what we want to do is to type in "brazil" somewhere and press a magic button that will just bring up rows 1 and 3, or type in "cheese" and bring up 1 and 3, or "fig" and bring up 2 and 3.

I realise that's possible with the Advanced Filters tool, but the primary user is not as technically confident as I am. I was really hoping for an easier solution, or at least one that's easier for them to process. Does anyone have any tips or straight-forward solutions?

I'd be more than happy to spend a little time setting something up as long as it was simple for the primary user. Any help would be appreciated!

Addendum:

I found some really promising Visual Basic scripts that would do the above with just one button, but unfortunately they won't work on Mac versions of Office, and Automator doesn't seem advanced enough to tackle this.

I also realise that a properly organised database (say, Filemaker) would be more appropriate to this sort of activity, but unfortunately that's not possible here.

posted by Magnakai to computers & internet (5 comments total)

Using the simple filter, with the custom setting (way down the bottom of the dropdown on that column), you can set a filter for 'containing' any string.
posted by pompomtom at 5:22 PM on March 20

That would work, except it needs to search over multiple columns. If there's a way of doing that with the regular Autofilter I'd be ecstatic, because that's exactly what I need. Is there?
posted by Magnakai at 6:21 PM on March 20

You said you can't use an "advanced" database... How about MS Access?

You can set up a form where the user types in the special word and clicks a button. It runs a query and pops up the records you want.
posted by powpow at 6:57 PM on March 20

Not the most elegant solution, but if you add two extra columns you can use (either at the end or beginning of the row), you can do this:

suppose your data start in row 2 (letting us use row 1 for the query)
in IU2 (or whatever the cell right after the end) set a formula like

=concatenate(A2,B2,C2,D2,...)

so that IV2 is equal to the contents of all the cells. This won't include spaces between cells, but you can add them if you'd like. Then, assuming the search term "brazil" is in A1, you can add a formula to IV2 to be

=if(search($A$1, IU2),1,0)


Then, drag the formulas for all the rows of data, and you can do a simple filter on IV where you hide 0 and show 1.
posted by i love cheese at 7:43 PM on March 20

Yeah, I'd chuck in an extra column of =A1&B1&C1... (same as contatenate()) etc, just making one huge string of each row.
posted by pompomtom at 5:05 AM on March 21