1

I've been wracking my head over this for a few days now, basically I tried to use a index/match/if/countif setup to try to get a unique list generated based on filters made by checkboxes. (not necessarily FOR unique values since all the names in data will be unique, but just so that the formula doesn't put in the same name over and over) Example Sheet Here's an example of what it looks like. Visual filter system top left, automated active filter list top right (for formulas, redundancies = no filters), character table data bottom right, automated visual character list bottom left (using formulas).

The top formula shown, for B10, is the only breakthrough I've found for using the active filters, but not sure how to apply the countif($B%8:B8,CharacterList) or similar function that won't duplicate the same result into the cells below it. (Forget the countif that's shown, it does nothing, part of my experimenting)

The John cell in the formula list doesn't have data matching the current active filters, and is using this:

=ARRAYFORMULA(INDEX($G$9:$G$23,MATCH(0,COUNTIF($B$8:B8,$G$9:$G$23)+IF($H$9:$H$23=$G$2:$G$4,1,0)+IF($I$9:$I$23=$H$2:$H$4,1,0)+IF($J$9:$J$23=$I$2:$I$4,1,0),0)))

The formula may have certain data switched around due to experimenting, mainly with the IF()'s, the +/* logicals, and trying a version that had IF((conditions),COUNTIF(),"") instead. It was one of the first formulas I've seen when I was searching for this problem. Other formulas I tried included a Small/Row formula integration which I figured out in the past for a different matter, but didn't help here. Also tried fiddling with the Unique/Filter functions, but I just couldn't figure it out and it kept giving me errors. I'm dumb, I tried to learn from many formulas but couldn't get it done (possibly using them wrong and/or overlooking something simple), so any help would be appreciated.

Improve this question
2
  • 1
    Welcome. Please share a copy of your spreadsheet (excluding any private or confidential information) with an example of a successful outcome. A screenshot is OK sometimes, but you've got a LOT of data in yours, and there's no substitute for the real thing.
    – Tedinoz
    Dec 3, 2019 at 0:49
  • Okay, sorry about that, I made a copy of it here: docs.google.com/spreadsheets/d/… I put in notes near the bottom of it
    – BManDope
    Dec 3, 2019 at 8:45

1 Answer 1

Reset to default
1

You want to build a query where certain criteria are triggered by selection of checkboxes. You have already set up formula to create dynamic lists of the checked criteria.

The data consists of:

  • the primary table (4 columns x 15 rows)
  • Criteria 1: Type - 3 x checkboxes, and 1 x dynamic list
  • Criteria 2: Team - 3 x checkboxes, and 1 x dynamic list
  • Criteria 3: Tier - 3 x checkboxes, and 1 x dynamic list

This answer consists of two parts:

1 - changes to the formula creating the dynamic lists: purpose to simplify, based on Query.

2 - Query formula to create the list of Characters that meet the criteria

Dynamic Lists

These formula are far more simple.

  • Type: =query(A2:B4,"select B where A =TRUE")
  • Team: =query(C2:D4,"select D where C =TRUE")
  • Tier: query(E2:F4,"select F where E =TRUE")

Character List

=query(G9:J23,"select G where "&"(H='"&textjoin("' OR H = '",true,G2:G4)&"')"&"and (I='"&textjoin("' OR I = '",true,H2:H4)&"')"&"and (J='"&textjoin("' OR J = '",true,I2:I4)&"')")

The primary feature of this formula is the use of a TEXTJOIN function to construct the criteria parameters based on the dynamic lists.

Improve this answer
6
  • Thank you very much! The dynamic list is definitely more easier to understand, though I'll need to dissect the character list formula to fully grasp all the symbols and "text" usages and their importance, but I'm sure it follows in similar fashion to the dynamic lists. Thanks again! I gave you a vote but its not counting cause I'm new, just so you know.
    – BManDope
    Dec 4, 2019 at 8:14
  • No worries. Have a look at What does it mean when an answer is "accepted"?
    – Tedinoz
    Dec 4, 2019 at 8:17
  • Actually, there is one problem with the dynamic list formula, its missing the redundancy function, which activates all filters whether all or none of them are checked. (should just be for if none are checked, actually) Not sure how to put it in since the language is different but I think I can look it up without issue, but thanks again!
    – BManDope
    Dec 4, 2019 at 8:31
  • Okay I just kinda shoehorned it in but I came up with =query(A2:B4,if(G6=TRUE,"select B","select B where A =TRUE"))
    – BManDope
    Dec 4, 2019 at 8:48
  • It would be nice to eliminate the "Redundancies" fields. How about =query(A2:B4,if(countif(A2:B4,TRUE),"select B where A =TRUE","select B"))
    – Tedinoz
    Dec 4, 2019 at 9:19

Your Answer

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

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