How to stack columns of differents length in Excel [duplicate]

Question

In have a woorkbook with many sheets. All sheets have the same format of content. I want to stack cells from one specific column of many sheets.

I give in cell A1 the list of the sheets : data;Blad7

I can display the content of the cells A1 :

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  Test; MAP(Sheets; LAMBDA(sheet;
                           INDIRECT("'" & sheet & "'!A1")
                          )
           );
  Test
)

I gives a 1Col x 2Rows result with the cells A1 of each sheet. Ok. Now I can check what I effectively want to stack :

=FILTER(INDIRECT("'" & "data" & "'!D:D"); INDIRECT("'" & "data" & "'!D:D")<>"")
=FILTER(INDIRECT("'" & "Blad7" & "'!D:D"); INDIRECT("'" & "Blad7" & "'!D:D")<>"")

I gives two spilled results of 18 and 52 lignes respectively. Perfect.

But when I try to combine these two test, like this, it doesn't work. I get #CALC! :

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  Test; MAP(Sheets; LAMBDA(sheet;
                           FILTER(INDIRECT("'" & sheet & "'!D:D"); INDIRECT("'" & sheet & "'!D:D")<>"")
                          )
           );
  Test
)

I also tried with BYROW instead of MAP. My final goal should by this :

=LET(
  Sheets; TRANSPOSE(TEXTSPLIT(A1; ";"));
  Test; VSTACK(MAP(Sheets; LAMBDA(sheet;
                           FILTER(INDIRECT("'" & sheet & "'!D:D"); INDIRECT("'" & sheet & "'!D:D")<>"")
                          )
           ));
  Test
)

Any idea why it doesn't work ?

OP there are two questions posted on this topic, @VBasic Sir and DjC has resolved them and with explanations why and what you need to use when you have such a situations. DjC-MAP_INDIRECT and VBasic-REDUCE_INDIRECT — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Jun 16 at 7:30
Finally, thanks to both of you, this is what I used : =DROP(REDUCE(""; TEXTSPLIT(A1; ";"); LAMBDA(acc;sheet; VSTACK(acc;FILTER(INDIRECT("'" & sheet & "'!D:D"); INDIRECT("'" & sheet & "'!D:D")<>"")) ) );1) — Belight
– Belight, Commented Jun 16 at 8:36

rotabor · Accepted Answer · 2025-06-16 07:22:41Z

0

The solution here is:

=DROP(REDUCE("";TRANSPOSE(TEXTSPLIT(A1; ";"));
    LAMBDA(a;sheet;
      VSTACK(a;LET(tr;INDIRECT("'" & sheet & "'!D:D");FILTER(tr;tr<>"")))));1)

The problem in your formulas is the MAP function which maps a cell to the cell only, no array support. The only way to join arrays is the REDUCE function in combination with VSTACK or HSTACK.

edited Jun 16 at 7:22

answered Jun 16 at 7:16

rotabor

5,6072 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to stack columns of differents length in Excel [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related