Google-sheets – Generating a list in Google sheets with checkmark filters

formulasgoogle sheetsgoogle-sheets-arrayformula

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.

Best Answer

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.

Related Topic