0

I have a number of sheets in my Google Sheets Doc. I would like to stack some rows from most of the sheets in another sheet.

Here is the code I am using:

=VSTACK(
   MAP(
     {"A"; "B"; "C"; "D"; "E"},
   LAMBDA(
     sheetname,    
     FILTER(
       INDIRECT(
         "'"&sheetname&"'!A1:A10"),
       INDIRECT(
         "'"&sheetname&"'!A1:A10")<>2)
     )
   )
)

This code, however, returns "Result should be a single row." error. Each lambda maps the name of the sheet to the range of rows. The question now is, how can I stack all of those ranges into one big table?

Reproducible sample

It's easy to stack a predefined number of ranges (example) but how could one stack the variable number of ranges that are returned from the MAP function? The length of my array is static in the example for simplicity but in reality it's variable and the array is returned by a function.

Alexey Subach
  • 11,903
  • 7
  • 34
  • 60
  • 2
    To help you get a more accurate answer, it would be beneficial if you could provide a minimal, reproducible example, such as a dummy sheet, that demonstrates the issue you're facing along with your desired result. This would allow the community to better understand your problem and provide targeted solutions. Including a reproducible example will greatly increase your chances of receiving a helpful response. – George May 31 '23 at 02:07
  • `REDUCE()` function may help. You need to specify sheet name in a range or inside function. Then use `FILTER()` function and store the results to accumulator of `REDUCE()` function. – Harun24hr May 31 '23 at 02:36
  • Is `sheetnames()` a custom formula? Note: you are missing a right parenthesis somewhere in there. – horanimal May 31 '23 at 04:31
  • @Harun24hr, could you let me know how to reduce two ranges into one? I tried this `=REDUCE({}, MAP( {"A"; "B"; "C"; "D"; "E"}, LAMBDA( sheetname, FILTER( INDIRECT( "'"&sheetname&"'!A1:A10"), INDIRECT( "'"&sheetname&"'!A1:A10")<>2) ) ), LAMBDA(x, y, {x;y}))` but it did not work – Alexey Subach May 31 '23 at 09:43
  • @horanimal Yes, `sheetnames()` is a custom formula returning all the names of the sheets in the given doc. I don't think any parenthesis were missing but anyway, with the link to reproducible sample anyone can play around – Alexey Subach May 31 '23 at 09:44

1 Answers1

2

You may try:

=let(Σ,reduce(,tocol(F:F,1),lambda(a,c,{a;indirect("'"&c&"'!A1:A10")})),
       filter(Σ,Σ<>2,Σ<>""))
  • tab names are listed in Column_F here

enter image description here

Updated answer:

=let(Σ,reduce(wraprows(,2,),tocol(F:F,1),lambda(a,c,{a;indirect("'"&c&"'!A1:B10")})),
       filter(Σ,index(Σ,,1)<>2,index(Σ,,1)<>""))
rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • Thanks! This works for a single column, however, I have multiple columns in my sheets I want to aggregate data from. If I specify the range as "A1:B10", I get the following error: "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.". Any ideas? – Alexey Subach May 31 '23 at 11:34
  • Try the updated answer to take into account `2` columns. Change the number within `wraprows` part to equal the number of columns your are importing from other tabs... – rockinfreakshow May 31 '23 at 13:03