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)
=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)
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
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
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
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
Every document in Google Sheets supports the “Chart Tools datasource protocol”, which is explained (in a rather haphazard way) in these articles: “Creating a Chart from a Separate Spreadsheet” “Query Language Reference” “Implementing the Chart Tools Datasource Protocol” To download a specific sheet as a CSV file, replace {key} with the document’s ID and {sheet_name} … Read more
in post-pandemic new world we can solve this with: =INDEX(FLATTEN(A2:A3&” “&TRANSPOSE(B2:B4))) to account for future expansion we can do: =INDEX(FLATTEN(FILTER(A2:A; A2:A<>””)&” “&TRANSPOSE(FILTER(B2:B; B2:B<>””)))) for 3 columns: =INDEX(FLATTEN(FLATTEN( FILTER(A2:A; A2:A<>””)&” “&TRANSPOSE( FILTER(B2:B; B2:B<>””)))&” “&TRANSPOSE( FILTER(C2:C; C2:C<>””)))) 4 columns: =INDEX(FLATTEN(FLATTEN(FLATTEN( FILTER(A2:A; A2:A<>””)&” “&TRANSPOSE( FILTER(B2:B; B2:B<>””)))&” “&TRANSPOSE( FILTER(C2:C; C2:C<>””)))&” “&TRANSPOSE( FILTER(D2:D; D2:D<>””)))) for more see: https://stackoverflow.com/a/74160711/5632629
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?
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))))),”@”)),”|”))
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