1

=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(TEXTJOIN(CHAR(10), 1, QUERY(TRANSPOSE("♦"&QUERY(FILTER('Source table'!$A$2:$G$6,'Source table'!$A$2:$A$6=$B3), "select Col2,Col3,':',Col4,Col5,Col6 label ':'''")),,999^99)), "♦", CHAR(10)), "^"&CHAR(10), ), " "&CHAR(10)&": "&CHAR(10), ": "))


1

If this is a time you should enter it as: 01:00:00 and format it like: And then you will just use simple SUM(A1:A2):


1

=ARRAYFORMULA({UNIQUE(INDIRECT("A2:A"&COUNTA(A1:A)))\ TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY(QUERY(A2:B; "select count(A) where A is not null group by A pivot B"; 0); "offset 1"; 0)<>""; QUERY(A2:B; "select count(A) where A is not null group by A pivot B limit 0"); )); ; 999^99)))})


1

While the above formulas may work for you, in case of huge imported arrays is better to import them separately in a sheet as: A1: =IMPORTRANGE("spreadsheetID";"sheet1!A4:A") B1: =IMPORTRANGE("spreadsheetID";"sheet1!F4:F") and then use it in VLOOKUP: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; 'hiddenimportsheet'!A:B; 2; 0))) This will slightly improve the ...


1

The workaround would be to temporarily disable ARRAYFORMULA by removing = sign or inserting ' in front of = sign - then do your row swapping and then again fix your array formula. The second workaround would be to delete the static value after you moved your row so the array formula could roll out instead of posting #REF! error. The 3rd option includes a ...


1

Functions like JOIN and FILTER can't be used in an array formula (ARRAYFORMULA function ) the way you are thinking (hope?). A formula using & (concatenate operator) could be used instead of JOIN but I don't think that could be possible to effectively replace FILTER by an ARRAYFORMULA "supported" function, so I think that you best bet is to use Google ...


1

paste in D2 cell and drag down: =ARRAYFORMULA(TEXTJOIN(", ", 1, REGEXREPLACE(TRIM(IFERROR(SPLIT(B2, ","))), TEXTJOIN("|", 1, IF(REGEXMATCH(TRIM(IFERROR(SPLIT(B2, ","))), TEXTJOIN("|", 1, TRIM(IFERROR(SPLIT(C2, ","))))), TRIM(IFERROR(SPLIT(B2, ","))), )), TRIM(IFERROR(SPLIT(B2, ",")))&" - CHECK"))) =ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(...


1

F2: =ARRAYFORMULA(QUERY({A2:A&B2:B, C2:D}, "select Col1,avg(Col2),avg(Col3) where Col1 is not null group by Col1 label avg(Col2)'',avg(Col3)''", 0)) I2 and drag down: ={STDEV(FILTER(C:C, A:A&B:B=F2)), STDEV(FILTER(D:D, A:A&B:B=F2))}


1

=ARRAYFORMULA(IF(LEN(A2:A), IF(LEN(B2:B), A2:A*B2:B, PI()*(A2:A/2)^2), ))


1

=ARRAYFORMULA({QUERY(SORT(UNIQUE({Income!A2:C; Expense!A2:C})), "where Col1 is not null"), {IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SORT(UNIQUE({Income!A2:C; Expense!A2:C})), "where Col1 is not null")),,999^99)), {TRANSPOSE(QUERY(TRANSPOSE(Income!A2:C),,999^99)), Income!D2:D}, 2, 0), 0), IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(SORT(...


1

=ARRAYFORMULA(IF(LEN(A2:A), TEXT(MONTH(A2:A)+(DAY(A2:A)/DAY(EOMONTH(A2:A, 0))), "0.00")*1, ))


1

You will need an array formula for that purpose: =ARRAYFORMULA(IF(LEN(B2:B), TODAY()-B2:B, )) or in the first row: ={"header"; ARRAYFORMULA(IF(LEN(B2:B), TODAY()-B2:B, ))}


1

=ARRAYFORMULA({SORT(TO_TEXT(UNIQUE(FILTER( {Sheet1!A2:A; Sheet1!B2:B; Sheet1!C2:C}, {Sheet1!A2:A; Sheet1!B2:B; Sheet1!C2:C}<>"")))), TRIM(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IF( QUERY(TO_TEXT(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE( QUERY(TRANSPOSE(IF(LEN(Sheet1!A2:C), "♠"&Sheet1!A2:C&"♦"&Sheet1!D2:D, )) ,,999^99)),,999^99), "♠")),"♦")), "...


1

=ARRAYFORMULA({Income!A1:E1, Expense!C1:D1, "FixedCost", "SubTotal"; INDIRECT("Income!A2:E"&COUNTA(Income!A2:A)+1), IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(INDIRECT("Income!A2:B"& COUNTA(Income!A2:A)+1)),,999^99)), {TRANSPOSE(QUERY(TRANSPOSE(Expense!A2:B),,999^99)), Expense!C2:D}, {2, 3}, 0), 0), TRANSPOSE(SPLIT(REPT(FixedCost!A2&"♠", ...


1

shorter alternative: cell D2: =UNIQUE(Sheet1!A2:A) cell E2: =STDEV(FILTER(Sheet1!B:B, Sheet1!A:A=D2)) cell E3: =STDEV(FILTER(Sheet1!B:B, Sheet1!A:A=D3))


1

I had a situation to randomly pick M or F in a column. I used this formula in Google Sheets: =IF(RANDBETWEEN(1,2) = 1,"M","F") Crude way, certainly not for long lists, but it works if your set of values is small.


1

You are able to paste CSV data as is into the spreadsheet and go to Data selecting Split text to columns... and even further specifying the delimiter: Also, you can simply use keyboard shortcut combo: LEFT ALT + D + E And as already mentioned SPLIT formula: =SPLIT("l,f,x,a,s,f"; ",") =SPLIT(A1; ",") For a range/array it would be: =ARRAYFORMULA(IFERROR(...


Only top voted, non community-wiki answers of a minimum length are eligible