3

I have been putting together a spreadsheet in Google Sheets that includes various data/information relating to the hundreds of characters in a novel I am reading. Two of the columns relate to the chapters that a given character, respectively, 1) actually appears in, and 2) is just mentioned in. (The data within this column, while technically numerical, is formatted as plain text.) As a way of allowing a user more easily to find the row for a specific character that they come across while reading, I have been attempting to utilize the new Google Sheets feature of filter views to show only rows/characters who appear or are mentioned within different ranges of chapters ("acts").

The other day, I discovered that filter views (and filters in general) are cumulative, so if I specify certain sets of chapters to display within the "Appearances" column, when I go to specify the sets to display within the "Mentions" column, I can only select those in rows whose "Appearances" values were specified.

I want a filter view to display rows for characters who appeared OR were mentioned in a given range of chapters. But filter views operate under "AND" logic. Is there a way to get filter views to display all of the rows I want them to, given my parameters? Or is there an alternate method of accomplishing this, perhaps using formulae?

(I did scour Google's documentation for possible alternatives---the REGEXMATCH formula seemed particularly useful for my purpose, though I am not sure how I would implement it.)

Improve this question
2
  • Can you give a sample of your data? If I understand correctly, you are entering something like a comma-separated lists of chapters into cells; that probably makes regexmatch necessary.
    – user79865
    Commented Sep 17, 2015 at 0:30
  • For example, I was making a filter view for chapters 41 and 42, but a major character who actually appears in both chapters was being hidden. The following data (comma-separated lists, as you inferred) come from this character's row, in the Appearances column and the Mentions column, respectively: "1,2,3,5,6 (brief),7,11,12,13,14,15,16,17,18,19,20,21,22,24,25,26,28,31,32 (brief),33,34,35,36,37,38,39,40,41,42" | "23,27,43"
    – SightSpirit
    Commented Sep 17, 2015 at 0:47

1 Answer 1

Reset to default
3

Instead of filter views, I would use the filter command. For example, suppose Sheet1 holds raw data, with A being a character, B the list of "mentioned" chapters, and C the list of "appeared" chapters. You can then add another sheet for "filtered" data, where the user enters chapter number in, e.g., cell A1 and immediately get the list of rows matching that number thanks to the command 

=filter(Sheet1!A2:C, regexmatch(Sheet1!B2:B,"\b"&A1&"\b") + regexmatch(Sheet1!C2:C,"\b"&A1&"\b"))

Here, each regexmatch scans its column (B or C) paying attention to word boundaries (\b) so that, e.g., "20,23,25" would not match "3". Addition plays the role of OR operator here.

If you are sharing this, you can allow editing but protect all cells except A1 on the second sheet: this way, the users can only change the chapter number for filtering.

There is a potential drawback: the changes to A1 are actual edits; i.e., what one user does affects everyone. If this is unacceptable, then filter views are the only option. You can circumvent the "OR" issue by creating a new column with 

=B2&","C2

i.e., concatenation of columns B and C. Then apply filter views based on that column. However, the filter view will require either

  • custom formula with regexmatch as above (not user-friendly at all)
  • different format of data to make the condition "text contains..." work: for example, you could format as ,2,4,10,13,16, so that the text to search for would be ,4, (Otherwise, there's the same issue of "13" matching "3".)
Improve this answer
3
  • So without adding a new column, I cannot simply use filter views, if I understand correctly? I must make a separate sheet? (This is entirely okay; I just hoped I could use filter views as a way of keeping things consolidated to as few sheets as possible.) And would I need a separate sheet for each filter? (This would actually get somewhat out of hand, however.)
    – SightSpirit
    Commented Sep 17, 2015 at 3:33
  • 1
    Yes, filtered views don't have the logic of "either this or that column matches". If using the filter command, it could well be within the same sheet if you prefer; it's just a matter of arranging columns and rows so that data doesn't overlap.
    – user79865
    Commented Sep 17, 2015 at 3:43
  • Alright, that makes sense. Thanks a lot!
    – SightSpirit
    Commented Sep 17, 2015 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.