Google Sheets: Query Function & Divider/Separator Row

Question

I've been trying to search for a solution but cant find anything similar.

I am currently using a query to get specific values from one sheet to another, but I am trying to add a separator when a specific column changes values. It looks like this right now:

But I would like it to look like this:

Any idea on how I would accomplish this?

Current Formula:

=QUERY('FM - Americas'!A:J,"select E,J where (E >= date '2020-10-01') and (E <= date '2020-11-30') Order By J, E",0)

Sheet Link: https://docs.google.com/spreadsheets/d/1ctr_c6cJYAmRrR78aIPkneyjOZAYtfvmyZD-PpXesgA/edit#gid=862432221

Thanks!

Kevin, for anyone here to even begin to help you as things stand, we've have to take time ourselves to open a spreadsheet of our own and enter your data by hand, then work on it, then explain to you what we did. You may not realize this, but those who offer help here do so on a volunteer basis. So you can best help us to help you by sharing a link to your sample spreadsheet (or a copy of it, or a representative sample from it), being sure to set that link's Share permission to "Anyone with the link can edit." If you'll do that, I'm confident that you'll receive efficient, effective help. — Erik Tyler
that could be easily done via a script, but it would break the query range so it wouldn't work in this context. But you could use a script to get this data structure on a different sheet for example. — soMario
Does this answer your question? Insert row between different data — marikamitsos
Sorry all, here is an example sheet. I have also edited the original post. Thank you! docs.google.com/spreadsheets/d/… — Kevin

kirkg13 kirkg13 · Accepted Answer · 2020-11-22T00:36:27

Well, this seems to work with the test data, and if your real data is very similar to what you've shown, maybe it will be helpful.

Try the following formula anywhere in your sheet. It returns two columns.

={                                      {query(A1:B,"where B='"& index(sort(unique(B$1:B)),1,1) &"'",0)};
    IF(counta(unique(B1:B))>1,{{"",""}; {query(A1:B,"where B='"& index(sort(unique(B$1:B)),2,1) &"'",0)}},{"Error 2",""});
    IF(counta(unique(B1:B))>2,{{"",""}; {query(A1:B,"where B='"& index(sort(unique(B$1:B)),3,1) &"'",0)}},{"Error 3",""});
    IF(counta(unique(B1:B))>3,{{"",""}; {query(A1:B,"where B='"& index(sort(unique(B$1:B)),4,1) &"'",0)}},{"Error 4",""});
    IF(counta(unique(B1:B))>4,{{"",""}; {query(A1:B,"where B='"& index(sort(unique(B$1:B)),5,1) &"'",0)}},{"Error 5",""});
    IF(counta(unique(B1:B))>5,{{"",""}; {query(A1:B,"where B='"& index(sort(unique(B$1:B)),6,1) &"'",0)}},{"Error 6",""})}

This counts how many unique values are in your column B, and then does a query for each unique value. The complexity was in stacking the results appropriately. Note how the blank rows are added, just after each IF statement check. This seems to work, but there may be a better or more efficient way that someone can suggest.

There is an overall array, shown by the {...}, that begins and ends the formula. There is a secondary array for each IF statement, and within that are two third level arrays, one for the blank row, and one for the QUERY statement results. Let me know if any of this is unclear.

Note that the formula needs to be extended if you may have several more possible regions (or values) in column B. Just duplicate the second last row as many times as needed, and adjust the numeric values in it, and in the final row, so they follow in sequence. Better error checking may also be useful.

Google Sheets: Query Function & Divider/Separator Row

1 Answers