Repeat each row N times in Google Sheets
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY( REPT(A1:A3&”♠”, 5), ,999^99), “♠”)))) =SORT(TRIM(TRANSPOSE(SPLIT(QUERY(ARRAYFORMULA( REPT(A1:A3&”♠”, 5)), ,999^99), “♠”))), 1, 0)
google-sheets-formula
How to use arrayformula with formulas that do not seem to support arrayformulas?
To achieve the result you’re looking for, you can use BYROW to supply the argument once per array: =BYROW({1;5},LAMBDA(row,INDEX(A2:B16,row,2))) BYROW sends the array argument provided once per row to the function inside LAMBDA. As a general formula, =BYROW(range, LAMBDA(row, your_formula(row))) If you want to send two arguments, use MAP instead. =MAP({1;5},{1;2},LAMBDA(arr_1,arr_2,INDEX(A2:B16,arr_1,arr_2))) This will get row … Read more
How to list all permutations without repetition?
The limitations described below are because of lambda functions. The first solution can be successfully implemented without lambda: =ARRAYFORMULA(QUERY(BASE(SEQUENCE(PERMUTATIONA(7,7)),7,7),”where not Col1 matches ‘.*((“&JOIN(“)|(“,SEQUENCE(7,1,0)&”.*”&SEQUENCE(7,1,0))&”)).*'”,0)) The trick here is to use regex to find unique elements using query…match. The only problem with this is memory size needed will exceed 10 million for 8 items PERMUTATIONA(8,8). But that … Read more
What factors determine the memory used in lambda functions?
If you’re affected by this issue, you can send feedback to Google: Open a spreadsheet, preferably one where you bumped into the issue. Replace any sensitive information with anonymized but realistic-looking data. Remove any sensitive information that is not needed to reproduce the issue. Choose Help > Report a Problem or Help > Help Sheets … Read more
IMPORTXML and right XPath from Bloomberg price [duplicate]
if all you are getting is #N/A error you have 3 options before turning to a script disable JavaScript. google sheets’ IMPORT formulae do not support the reading of JS content/elements. after you disable JS on your URL and the element you wish to scrape is not present there is 99.9% certainty you can give … Read more
I get Formula parse error with all formulas
This might be a locale issue. Some locales use a comma as argument separators, others (e.g. some European countries) use semi-colons (;). So maybe try changing your comma’s into semi-colons?
Repeat whole row N times based on column value in Google Sheets
Give a try on below formula- =INDEX(SPLIT(FLATTEN(SPLIT(JOIN(“”,INDEX(REPT(BYROW(A2:D3,LAMBDA(x,TEXTJOIN(“|”,0,x)))&”@”,E2:E3))),”@”)),”|”)) To make it dynamic spill array, use- =INDEX(SPLIT(FLATTEN(SPLIT(JOIN(“”,INDEX(REPT(BYROW(A2:INDEX(D2:D,MATCH(“zzz”,D2:D)),LAMBDA(x,TEXTJOIN(“|”,0,x)))&”@”,E2:INDEX(E2:E,MATCH(9^9,E2:E))))),”@”)),”|”))
How to compare dates or date against today with query on google sheets?
There’s no today() in Query. Use now() instead: =query(sheet1!A3:N, ” select I,M where I = ‘Singapore’ AND M > now() “,0) Or if you want now() without time(equivalent to TODAY()), use: todate(now()) For this to work, provided you have all the correct dates in M in any format, which Google sheets recognises (i.e., the formula … Read more
Query is ignoring string (non numeric) value
As MattKing already mentioned in the comments, query doesn’t like mixed data type in a single column. This is simply because, for query purposes, the majority data type determines the data type of the column. The minority data types are considered as null values. There are still ways to circumvent the issue. Here are the … Read more
ARRAY_LITERAL, an Array Literal was missing values for one or more rows
…understanding the ARRAY_LITERAL ERROR: until both queries/filters/formulas output something then all is good: however if one of those queries/filters/formulas doesn’t have anything to output it outputs #N/A – No matches are found in QUERY/FILTER evaluation. – the issue is that #N/A is only in the 1st cell: but array expects that matrix on both sides … Read more