Trying to split a google sheets query output array into 1st half and 2nd half-CodePudding

I have a need to output a google sheets QUERY function in two halves, each half on a different sheet (these get exported into other software). I need to preserve the original ordering, which is prepared on a third sheet which is the source of the data for the query. This preservation of order is the part I'm hung up on, plus my current mostly-working solution is pretty convoluted, would love to simplify.

Here's what I've got:

1st half formula (retains original order)

=SORTN(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0),CEILING(ROWS(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0))/2))

2nd half formula (resorts & loses original order, and calls the query 7 times to get the job done)

=sortn(SORTN(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0),FLOOR(ROWS(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0))/2),0,index(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0),0,1),false),ROWS(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0)),0,index(SORTN(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0),FLOOR(ROWS(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0))/2),0,index(QUERY(mainList!A3:U946,"SELECT T,U WHERE A!='' and P!=''",0),0,1),false),0,1),true)

I'm using ROWS() on the query to count the length, the FLOOR(ROWS()/2) and CEILING(ROWS()/2) to get the right number of results regardless of odd/even query length. The second half formula runs it through SORTN() twice but that's part of the problem, as it's forcing me (I think) to sort based on one of the columns and not just reversing the order without resorting (can't resort because the software will sort x-17 above x-6, and my third 'source' data sheet has already solved that ordering issue). I feel like there should be a way to do this without having to call QUERY seven times to get the job done for the second half formula. Any wizards out there? Thanks in advance!

link to the spreadsheet

the data comes from 'mainList' and the split queries are on 'EV1' and 'EV2'

CodePudding user response：

=ArrayFormula(REGEXREPLACE(QUERY(SORT(REGEXREPLACE(mainList!A3:U,"(-)(\d)$","$10$2"),20,1),"SELECT Col20,Col21 WHERE Col1!='' and Col16!='' LIMIT "&CEILING(COUNTA(QUERY({mainList!A3:U},"SELECT Col20 WHERE Col1!='' and Col16!=''",0))/2),0),"(-)0(\d)$","$1$2"))

=ArrayFormula(REGEXREPLACE(SORT(QUERY(SORT(REGEXREPLACE(mainList!A3:U,"(-)(\d)$","$10$2"),20,0),"SELECT Col20,Col21 WHERE Col1!='' and Col16!='' LIMIT "&FLOOR(COUNTA(QUERY({mainList!A3:U},"SELECT Col20 WHERE Col1!='' and Col16!=''",0))/2),0),1,1),"(-)0(\d)$","$1$2"))

CodePudding user response：

=ArrayFormula(QUERY(SORT(mainList!A3:U,2,1),"SELECT Col20,Col21 WHERE Col1!='' and Col16!='' LIMIT "&CEILING(COUNTA(QUERY({mainList!A3:U},"SELECT Col20 WHERE Col1!='' and Col16!=''",0))/2),0))

=ArrayFormula(SORT(QUERY(SORT(mainList!A3:U,2,0),"SELECT Col20,Col21 WHERE Col1!='' and Col16!='' LIMIT "&FLOOR(COUNTA(QUERY({mainList!A3:U},"SELECT Col21 WHERE Col1!='' and Col16!=''",0))/2),0),1,1))

Chewed on it a bit more. The first solution used REGEXREPLACE to normalize the alphanumeric ID tags for sorting purposes. Then realized we already accomplished the normalizing and sorting in mainList!B so SORT(mainList!A3:U,20,0) which sorts on column mainList!T which is not normalized, becomes SORT(mainList!A3:U,2,0), pulling in the data pre-sorted based on mainList!B.

With these changes, the solution more clearly illustrates the functionality necessary to return the top and bottom half of the queries separately. The top half function queries the original dataset in the intended order and limits results to the first half of entries. The bottom half queries a reversed set of the original data and returns the first half of those entries, then reverses them again to get the right final order.