I have an Excel sheet serving as a master spell list for a tabletop game. Each row (save for row 1) contains the data for a spell: Name, Target, Range, Duration, Function, Effect, Subject, Level, and Description. (Row 1 contains headings for the filters.)
There is a specific set of values for columns B through G (Target through Subject), and those values define which spells a character might be capable of casting. For example, "Agony" has Target: Individual, Range: Seen, Duration: Sustained, Function: Genesis, Effect: Diminish, Subject: Body. If a caster doesn't know how to cast "Subject: Body" spells, he or she cannot cast Agony, despite knowing all of the other criterion.
I want the spell list to be filterable, so that a player may be able to easily find what spells are available. There is one little problem: some spells have multiple values for Effect and/or Subject.
For the moment, I have the relevant values alphabetically sorted within their cell as a comma-delimited list. For example, "Cloak of Bats" has Effect: "Animate, Combine, Separate" and Subject: "Animal, Construct". This setup helps to reduce redundancies in the filter dropdown list (the game book does not sort the effects/subjects alphabetically), but I still have more checkboxes than I'd like in these two columns.
I want my Effect filter to have only 9 checkboxes: "Select All" and the 8 different Effects in the game (as opposed to the 26 that are currently there). If I select Animate, Combine, and Separate, then Cloak of Bats will show up; if I deselect Separate, Cloak of Bats will disappear. Similarly, I only want 20 checkboxes in my Subjects filter, rather than the 53 that currently appear.
If possible, I'd like to accomplish this task without VB, but I understand that may not be feasible. If VB is the only option for this problem, that's fine.
I want this:
To look like this:
Without changing my data set.
Changing "Effect" into three separate columns (or four, in the case of Subject), is not a useful solution to me. Compare for example the cases of "Animate, Forge, Modify," "Animate, Modify," "Forge, Modify," and "Modify, Reveal." In almost all cases, I would be forced to repeat the same filters on three or four different columns.