Excel Dynamic Arrays and MAKEARRAY inside LET not working when using variable as input for rows number

Question

I'm running in this problem where MAKEARRAY doesn't provide the correct result when using it with a varible as input.

I'm try to build kind of a Gantt chart. I have a list of weeks, for every week I find the proper activities belonging to it. The issue is that I need to properly align activities with the weeks before.

Normal result:

Wanted result:

The list of activties are sorted by date, thus i just have to match the first in the column and add extra row to get to the desired line.

I'm using this formula:

  =LET(
dataAll;        PromoTbl[[SO_inizio]:[SO_ fine]];
daySel;         I$5;
cliente;        DASHBOARD!$B$2;
filtroCliente;  (PromoTbl[[L6]:[L6]]=cliente);
filterWk;   ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel+7;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel;row)))+1)>=1);
filterWkBef;  ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel-1;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel-7;row)))+1)>=1);
dataWk;         SORT(FILTER(dataAll;filtroCliente*filterWk;"NA"));
dataWkBef;      SORT(FILTER(dataAll;filtroCliente*filterWkBef;"NA"));
listWk;         BYROW(dataWk;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
listWkBef;      BYROW(dataWkBef;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
firstRow;       TAKE(listWk;1);
idx;            VALUE(IFNA(MATCH(firstRow;listWkBef;0);1));
spaces;         MAKEARRAY(idx;1;LAMBDA(r;c;"space"));
newArr;         VSTACK(DROP(spaces;1);listWk);
newArr
)

Sorry for ; separator instead of comma (my italian setup).

Believe me that every single step up to "idx" works as expected.

The problem is in "spaces" onward. If I use the "idx" variable in MAKEARRAY I only get 1 single extra row added. If I test with a fixed number (i.e. 4) instead of the "idx" variable, I get the expected result.

This is what I get (notice "space" text added to highlight the problem, and I know the code will return extra space even when not needed). Just to simply. Notice in column 22/02/2025 the idx variable properly returns 4 as result of the MATCH formula. But I only get 1 extra space from MAKEARRAY instead of 4.

Any clues?

Adding a link to a test workbook. https://docs.google.com/spreadsheets/d/1wfLMr9aeyL83CFK85KqUsKMjafmGsQRr/edit?usp=sharing&ouid=106172219459643397407&rtpof=true&sd=true

What happens if you change the idx formula to, for example VALUE(IFNA(MATCH(firstRow;listWkBef;0);4)); — in other words, are you sure that idx is getting the correct value? Dates can be weird with MATCH and LOOKUP, because sometimes they are treated a Strings, sometimes as Numbers, etc — Chronocidal
– Chronocidal, Commented Apr 5 at 9:05
Made the try and it doesn't work, as expected as MATCH returns correcty index 4 (so no fallback to hardcoded 4). I'm pretty sure Idx gets populated corretly and it is a number (wrapped in VALUE for extra safety). If I return idx from the LET this what I get: 08/02/2025 -> 1,0 15/02/2025 -> 1,0 22/02/2025 -> 4,0 As you can see, it is a number. — Paolo De Laurentiis
– Paolo De Laurentiis, Commented Apr 5 at 9:26
Hmm... Is there a reason why drop is in lower case, but your other functions are all in upper case? You don't have a Named Range or something that shares the name with the function? — Chronocidal
– Chronocidal, Commented Apr 5 at 10:59
Could you start from the beginning and debug to see whether each of the steps are working or not correctly? May be the drop as mentioned could be a reason if there is any named reference however i feel its a typo — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Apr 5 at 11:09

Paolo De Laurentiis · Accepted Answer · 2025-04-05 17:00:23Z

Thanks to Mayukh Bhattacharya that solved the problem by suggesting to wrap the idx inside a MAX(idx, 1) function when using it inside MAKEARRAY!

Final function looks like that:

=LET(
dataAll;        PromoTbl[[SO_inizio]:[SO_ fine]];
daySel;         D$6;
cliente;        DASHBOARD!$B$2;
filtroCliente;  (PromoTbl[[L6]:[L6]]=cliente);
filterWk;   ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel+7;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel;row)))+1)>=1);
filterWkBef;  ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel-1;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel-7;row)))+1)>=1);
dataWk;         SORT(FILTER(dataAll;filtroCliente*filterWk;"NA"));
dataWkBef;      SORT(FILTER(dataAll;filtroCliente*filterWkBef;"NA"));
listWk;         BYROW(dataWk;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
listWkBef;      BYROW(dataWkBef;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
firstRow;       TAKE(listWk;1);
idx;            MAX(IFERROR(MATCH(firstRow;listWkBef;0);1);1);
spaces;         MAKEARRAY(idx;1;LAMBDA(r;c;""));
newArr;         DROP(VSTACK(spaces;listWk);1);
newArr
)

P.b · Accepted Answer · 2025-04-06 09:04:01Z

1

If I understood things correctly, this would also be an approach:

=LET(
customer,     D2,
dates,        E6:L6,
first,        MIN(dates),
last,         MAX(dates),
startdates,   PromoTbl[SO_inizio],
enddates,     PromoTbl[SO_ fine],
filtered,     FILTER(PromoTbl[[SO_inizio]:[SO_ fine]],(PromoTbl[L6]=customer)*(startdates>=first)*(startdates<=last)+(enddates>=first)*(enddates<=last)),
DROP(REDUCE("",dates,
     LAMBDA(h,d,
            HSTACK(h,
                   IF((TAKE(filtered,,1)<=d)*(DROP(filtered,,1)>=d),
                      BYROW(filtered,LAMBDA(f,TEXTJOIN("|",,TEXT(f,"dd mmm yy")))),
                      "")))),
     ,1))

Where dates is the basis of creating a filter on your data.

first and last are the first and last date in dates.

Next filtered filters the start and end dates from your data if they overlap values between first and last

Next reduce returns the combination of the filtered values if they meet the specific value from dates row wise. If it meets, it shows, else it returns blank

Or the same using MAKEARRAY:

=LET(
customer,    D2,
dates,       E6:L6,
first,       MIN(dates),
last,        MAX(dates),
startdates,  PromoTbl[SO_inizio],
enddates,    PromoTbl[SO_ fine],
filtered,    FILTER(PromoTbl[[SO_inizio]:[SO_ fine]],(startdates>=first)*(startdates<=last)+(enddates>=first)*(enddates<=last)),
MAKEARRAY(ROWS(filtered),COUNT(dates),
   LAMBDA(r,c,
LET(f,INDEX(filtered,r),
    REPT(TEXTJOIN("|",,TEXT(f,"dd mmm yy")),(@f<=INDEX(dates,c))*(DROP(f,,1)>=INDEX(dates,c)))))))

edited Apr 6 at 9:04

answered Apr 5 at 19:12

P.b

12k2 gold badges15 silver badges34 bronze badges

1 Comment

Paolo De Laurentiis Apr 6 at 21:01

This is amazing. But the limitation is that it forms an array with as many rows as the filtered array, and continue to stack each result at the line the appear in the array itself. Not exactly what I wanted. But anyway you are a super pro and gave me amazing clues on how to move forward. I'm posting my final solution right now.

Paolo De Laurentiis · Accepted Answer · 2025-04-06 21:15:16Z

I reached a final solution by combining the inputs from both Mayukh Bhattacharya and P.b that provided amazing solutions and clues. Now the formula spills both horizontally and vertically, and gives the following output (added some filling to empty cell to use later with conditional formatting).

Here is the code (I'm sure some super pro will find a way to optimize for performance but in my use case I have just few dozens line to process in filteredData array).

=LET(
customer;     DASHBOARD!$B$2;
dates;        $D$6#;
FILLER;       "@";
first;        MIN(dates);
last;         MAX(dates);
startdates;   PromoTbl[SO_inizio];
enddates;     PromoTbl[SO_ fine];
filteredData; SORT(FILTER(PromoTbl[[SO_inizio]:[SO_ fine]];(PromoTbl[L6]=customer)*((startdates>=first)*(startdates<=last)+(enddates>=first)*(enddates<=last)));1);
resFull;
   REDUCE("";dates;
     LAMBDA(acc;daySel;
      IFERROR(HSTACK(acc;
             LET(
                     listWk;REDUCE(FILLER; IF(
                                          (CHOOSECOLS(filteredData;1)<=daySel+7)*(CHOOSECOLS(filteredData;2)>=daySel);
                                           BYROW(filteredData;LAMBDA(row;TEXTJOIN("|";;TEXT(row;"gg mmm aa"))));FILLER);
                                           LAMBDA(a;v;IF(v=FILLER;a;VSTACK(a;v))
                                          ));
                     firstRow;       TAKE(DROP(listWk;1);1);
                     accCols;        COLUMNS(acc);
                     accLastCol;     TAKE(acc;;-1);
                     idx;            IF(accCols=1;0;IFERROR(MATCH(firstRow;accLastCol;0);0));
                     spaces;         MAKEARRAY(MAX(idx-1;1);1;LAMBDA(r;c;FILLER));
                     newArr;         DROP(VSTACK(spaces;listWk);1);
                     newArr
                     )
          );FILLER)
        )
);
res;           DROP(resFull;1;1);
res
)

To give this group of amazing Excel pro a new challenge, I telling you that my end goal was to reach somthing similar to the below:

Where removing extra repetitions, adding "center accross selection" to every cell, and adding some conditional formatting. Unfortunately I now discovered that "Center Accross Selection" does not work within a spilled range, even if the cell aside has a "" value. It is considered full cell and thus the label does not center. I guess I have to add some VBA to copy and run the formula in place when inputs change and finally paste to values. Unless you can find another great solution!

Collectives™ on Stack Overflow

Excel Dynamic Arrays and MAKEARRAY inside LET not working when using variable as input for rows number

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related