Search code examples
arraysif-statementgoogle-sheetsuniquegoogle-query-language

Google Sheets: EXCLUDE hidden rows in query pulling unique data using indirect function

I have an Apps Script pulling every tab name in my Google Sheet into a list. I'm then using a Query to pull all unique Names (from a Name column) from each of those tabs, with the Indirect function.

There are currently hidden rows in each of the tabs (there are 15 tabs total). The Query I have is currently pulling the Names in those hidden tabs.

How do I tell the Query to pull all unique data EXCEPT the hidden rows? Here is the existing Query formula I have which is including hidden row data (I'm only including 2 tabs of data here to shorten the formula for this example):

=UNIQUE(QUERY({IF(B2="", {"","","",""}, INDIRECT(B2));IF(B3="", {"","","",""}, INDIRECT(B3))},"Select Col4 where Col4 is not null order by Col4 label Col4 'Names'",1))

The {"","","",""} is because I have INDIRECT formulas for future anticipated tabs which don't yet exist in the list of tabs, so I won't have to update the formula every time a new tab is added. For excluding the hidden data, I've looked into the SUBTOTAL formula but I'm having trouble applying it to this situation.

Here's a mock-up, smaller scale example of the sheet I'm working with. All of the names in the Names list on the Tabs tab that start with "Hidden" are in hidden rows on one of the tabs the query is pulling from. I want to exclude those Hidden names/rows from the query output.

Thanks in advance!

Solution

  • SUBTOTAL is the way but it does not work with arrayformula so you will need to drag it down into each row. see your sheet.

    =UNIQUE(QUERY({
 IF(B2="",  {"","","","",""}, INDIRECT(B2));
 IF(B3="",  {"","","","",""}, INDIRECT(B3));
 IF(B42="", {"","","","",""}, INDIRECT(B4))},
 "Select Col4 
  where Col4 is not null 
    and Col5 = 1 
  order by Col4 
  label Col4 'Name'", 1))

    also note the range change from A:D to A:E

    enter image description here