Excel Advanced multi-column filtering with multiple acceptable data combinations

Question

I have a large dataset with 4 columns of interest containing text, namely a pokemon. The columns "move 1" to "move 4" contain different motions, and each row has a different combination. eg.

" A | B | C | D | E". " 1 Pokemon | Move 1 | Move 2 | Move 3 | Move 4". " 2 Igglybuff | Tackle | Tailwhip | Sing | Attract". " 3 Wooper | Growl | Tackle | Rain Dance| Dig". 

~ 1000 more

My problem is this: I want to filter this dataset for strings (pokemon) containing a certain combination of moves from the list. eg. I want to find which Pokemon has "Growl" and "Tackle". These movements can appear in any of the movements 1 through 4 (otherwise the order of movements is unimportant) How would I go for filtering for such a result. I have similar situations in which I would like to search for a combination of 3 or 4 moves, the specific order of which is not important, as well as the search for a specific Pokemon that has a certain combination of moves.

I tried using functions like COUNTIF to no avail. Help / Ideas are greatly appreciated

Answer 1

There are a number of options for advanced filtering in excel that you might consider:

Option 1 - Advanced Filters

Advanced filters give you the ability to request multiple criteria (this is what you need). You can also easily do this as many times as you want to generate final data sets using each filter. Here is a link to the extended filter section for Microsoft Excel 2010, which is almost identical here in 2007 . This would be a great place to start if you want to go beyond the simple use of basic formulas.

If you are following this route, follow the instructions on the site in terms of steps:

• Paste in the various criteria that you have selected in the top rows of your spreadsheet, and specify these rows in the list range

  • Set the range of criteria in a place to store all your data on one sheet

  • Run the filter and look at the data received. You can easily count the number of records in this reduced dataset.

Option 2 - Summary Tables

Another option you can see here is to use Pivot tables. Pivot tables and pivot charts are simply phenomenal tools that I use at the workplace every day to do exactly what you are looking for.

Option 3 - Using Visual Basic

As a third option, you can try using visual base code to write a solution. This will give you perfect control, as you can precisely specify ranges for each of the conditions. Unfortunately, you will need to understand the VB code in order to use this solution. There are some excellent online resources that can help with this.