This would be much easier to solve on the 1* data sheet, but I am trying to have grouped data with a weekly quantity broken down into a daily # to make work easier further down the line.
=query({ARRAYFORMULA(if(BinCountData!A3, "Store0",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!A3:B;
ARRAYFORMULA(if(BinCountData!E3:E,"Store1",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!E3:F;
ARRAYFORMULA(IF(BinCountData!I3:I,"Store2",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!I3:J;
ARRAYFORMULA(IF(BinCountData!M3:M,"Store3",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!M3:N;
ARRAYFORMULA(IF(BinCountData!Q3:Q,"Store4",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!Q3:R;
ARRAYFORMULA(IF(BinCountData!U3:U,"Store5",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!U:V;
ARRAYFORMULA(IF(BinCountData!Y3:Y,"Store6",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!Y3:Z;
ARRAYFORMULA(IF(BinCountData!AC3:AC,"Store7",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!AC3:AD;
ARRAYFORMULA(IF(BinCountData!AG3:AG,"Store8",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!AG3:AH;
ARRAYFORMULA(IF(BinCountData!AK3:AK,"Store9",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!AK3:AL;
ARRAYFORMULA(IF(BinCountData!AO3:AO,"Store10",)(BinCountData!B3/7)*Row(1:7)^0), BinCountData!AO3:AP
}, "
Select *
where Col1 <>''
label Col1 'Date', Col2 'Store'
")
What would be the replacement for *Row(1:7)^0 to make them go to sequentially in the same columns?
Advertisement
Answer
if you want to distribute it into columns instead of rows you can do:
*COLUMN(A:G)^0
or:
*TRANSPOSE(ROW(1:7)^0)
or:
*SEQUENCE(1, 7, 1, 0)
update
try:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(FLATTEN( FILTER(BinCountData!A3:AR, MOD(COLUMN(BinCountData!A:AR)-1, 4)=0))+SEQUENCE(1, 7, 0)&"×"&FLATTEN( FILTER(BinCountData!A1:AR1, MOD(COLUMN(BinCountData!A:AR)-1, 4)=0)&"×"& FILTER(BinCountData!A3:AR, MOD(COLUMN(BinCountData!A:AR)-2, 4)=0)/7)), "×"), "where Col3 <> 0 order by Col2 label Col1'Date',Col2'Store',Col3'Bin/Day' format Col1 'yyyy-mm-dd'"))
