How to send values to automatic sequential rows in an arrayformula on gsheets?

user_15085200 0
Viewed 46

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?

google-sheets-formula google-sheets sql
1 Answers

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'"))

enter image description here

demo spreadsheet

user_56326290
Related