CODEWITHSUNDEEP
google-sheets
Gabe
Gabe

Reputation: 41

Google Sheets - how to filter a range for rows that contain a substring in any column

https://docs.google.com/spreadsheets/d/1vtfRQRMTmH2JfZasDTwzVIeg4oDRKC_Qzb_Tz8i9TBE/edit#gid=0

Named Range: 'Registration' = A:F

Goal: H2 filters Column A for any row in Registration that contains the word "Yes".

I've tried looking into query() and filter() but those seem to require conditions by column(s). And I wasn't able to get lookup() to work either. And ideally I'd like the flexibility in case other columns are added or moved around.

I'd appreciate any help!

Upvotes: 0

Views: 1701

Answers (1)

General Grievance
General Grievance

Reputation: 4988

Well, it's not pretty, but QUERY does work:

=ArrayFormula(QUERY(
  Registration,
  "select A 
  where "&
    JOIN(" or ", CHAR(65+SEQUENCE(1,COUNTA(INDEX(Registration,1,0))-1))&"='Yes'")
  )
)

Remarks:

  • Index is interesting in that if you pass a 0 into either argument, it returns all rows or columns or both. I use this to count the number of columns excluding A in your named range.
  • Then I sequentially generate A, B, C, etc. using the ASCII code. It only works up to Z, though, I'm afraid. If you need more, you can clone the table and reference it with Col+N and generate a sequence based on that.
  • The query checks each of those columns per row for a "Yes".
  • And you can add as many Columns or Rows as you like. Just don't forget to expand your Named Range as needed.

Upvotes: 4

Related Questions