Excel - how do I use a column for "tags" to filter on

Question

I have an excel sheet containing data (Runes for the game Leage of Legends, for what it's worth):

Name, Type, Price, Effect, ...

I want to add an extra column on which I can filter, that contains tags (much like the tags assigned to questions here). So for example, one row might have tags "AD, PERLEVEL, RECOMMENDED". I can simply put the tags in a cell, comma separated like this, and use the "contains" filter. But something that worked like Excel 2010's autofilters (listing all the values in use, and allowing you to tick and untick values you want to select) is really what I'm after.

Is it possible to achieve something like this?

If a tickable list isn't possible, I can use a filter search to select values (so that part is achievable). If I could somehow display the unique tags, that would do the job.

Answer 1

If you're okay adding some cells to your spreadsheet, you can do this via a slightly kludgy means. Make a new 2x2 range (I put these into A9:B11) that shows the filter criteria, and whether you want them on or off. For example:

AD                  FALSE
PERLEVEL            TRUE
RECOMMENDED         TRUE

Now into your original range you can add another cell containing the array formula:

{=MIN(SIGN(ISERROR(FIND(IF($B$9:$B$11,A$9:A$11,"***"),C1))))=0}

If you're not familiar with array formulas - I'm showing it with the braces, but you should enter it without and hit Ctrl-Shift-Enter instead of just enter.

Make sure that the formula points to your entire filter criteria range.

That will generate a new column in your original list showing "TRUE" if it is to appear in the filter, or "FALSE" if it isn't. You can then filter on the value of that column (and re-apply the filter when you change the criteria).

To make the checkboxes, you can make a CheckBox form control which stores its value in the TRUE/FALSE column on the filter criteria.