Another excel question

December 10, 2020 7:21 PM

I have a spreadsheet and I need to filter on a column that can have multiple items in one cell, but I want the filter to only have single items to choose from.

Example:

Name                Favorite Fruit
Jane                 Bananas
John                 Oranges
Alex                  Apples, oranges
Mary                 Strawberries, bananas
Mark                 Bananas

With a regular filter, if I want to see all the people who like bananas, I would need to select the following items in the second column filter:

Bananas
Strawberries, bananas

I want to have a filter that shows all the items in that column as single items only (so it would basically be separating any multiples in the cells). So I only need to filter on Bananas, and my filtered list would have Jane, Mary and Mark.

Is this possible?

posted by triggerfinger to Computers & Internet (5 answers total)

Not sure if this meets your needs, but the regular Excel filter lets you basically filter like a search- so typing in “bananas” would select both your items that include bananas. I’m not sure what you mean by “second column filter” though- are you doing this in a Pivot Table?
posted by MadamM at 7:37 PM on December 10, 2020 [1 favorite]

Or I guess I should clarify, are you using the visual filter on a column, or the filter function, or some other kind of filter that may well be hiding in Excel somewhere? If you’re using the filter function, this may help.
posted by MadamM at 7:40 PM on December 10, 2020

You can use the concise, if somewhat obtuse, paring of ISNUMBER and SEARCH functions. Ie:

=ISNUMBER(SEARCH("Bananas",A1))

Returns TRUE if 'Bananas' is a substring in the contents of A1, and FALSE if it is not.
posted by mce at 7:40 PM on December 10, 2020 [5 favorites]

mce's solution will cause problems if the favorite fruits have overlapping substrings, e.g., if Grape and Grapefruit are both options. You can address that by looking for Grape, if you can also ensure that there's always a comma at the end of the second column (or by looking for ,Grape if you can ensure there's always a comma at the beginning).

There are probably other clever ways to do this in Excel, but you're fundamentally trying to work with unnormalized data in a way that's not going to be convenient.
posted by likedoomsday at 7:03 AM on December 11, 2020

Rather than a kludgy solution internal to Excel, I regularly handle this sort of thing by normalizing/cleaning the data first, usually in OpenRefine.
posted by aspersioncast at 10:51 AM on December 11, 2020