Is it possible to store multiple values in an excel cell?

Question

So I'm doing an analysis of values in a database and I figured the simplest way to do this is to use a pivot table. The problem I have is that some of the categories have multiple values in them. To show what I mean, I've included a sample of my excel file in this link: Excel Sample.

What I want to be able to do with my pivot is to, for example, filter Exposure Class by "N" and have all three entries with "N" show up. Is it possible to do this? I do have to note that this analysis doesn't have to be done in a pivot but the pivot does make the rest of the analysis a lot easier.

Answer 1

Short answer: No -- each Excel cell contains a single value

Long answer: Yes, of course! That single value can be a delimited string which splits into multiple fields, each of which corresponds to a value. Also, you can smuggle information into a cell using comments, formatting, etc. -- although such information isn't easy to manipulate without VBA.

If the data in the cell looks something like "C2, S1, N" you can test if something like "S2" appears in it. Say that the string is in A1. Then the following formula returns TRUE or FALSE depending in whether or not S2 appears in it:

=IF(IFERROR(SEARCH("S2",A1),0) > 0, TRUE, FALSE)

(SEARCH isn't case-sensitive. Use FIND if you want a case-sensitive search). I don't have a lot of experience with pivot tables but I know that it is possible to filter a pivot table by a formula. You should be able to pull up all records in which e.g. "S2" appears in it.