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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With