I hope someone could help with this one. I’m not sure if this is possible but I would like to know if there is any way to insert a blank row after every change of Data in ColumnA(Country) which is the result of a query function.
I want to break on Country (Column A) and Accounts" (Column B) as per the DesiredOutput sheet.
Here is the sample file:
https://docs.google.com/spreadsheets/d/10R3pOblC3_u6E5-l1LqvaZzMlrZWoVuYrebVMW2wm9w/edit
BCBA Summary tab is the result of the query referencing the Raw_Data tab.
The DesiredOutput tab is my aim since I will have more than 20 accounts that will be added in the future and it will be easier for me to read the data.
I wonder if there is any way to get the DesiredOutput?
I tried this How to automatically insert a blank row after a group of data, successfully inserted a row, however, it is for every other data. I hope you could help me.
Thank you so much.
Best Answer
You want to insert a new line at each change of County (Column A).
There are many ways that this could be done. These have been sourced from: How to automatically insert a blank row after a group of data. on Google Docs Help (Community).
Option#1 based on
TEXTJOIN
:=ArrayFormula(iferror(split(transpose(split(textjoin(",",true,if(len('BCBA Summary'!A3:A),{'BCBA Summary'!A3:D,if('BCBA Summary'!A3:A<>'BCBA Summary'!A4:A,";;",";")},)),";",true,false)),",",true,true)))
OR
Option#2 based on
VLOOKUP
/SORT
/FILTER
:=ARRAYFORMULA(IFERROR(VLOOKUP(SORT({FILTER(ROW('BCBA Summary'!A3:A),'BCBA Summary'!A3:A<>"");FILTER(ROW('BCBA Summary'!A3:A),'BCBA Summary'!A3:A<>"",'BCBA Summary'!A3:A<>'BCBA Summary'!A4:A)+0.5}),{ROW('BCBA Summary'!A3:A),'BCBA Summary'!A3:D},{2,3,4,5},0)))
Both formula yield the same result.