Forum Discussion

bozhkov's avatar
bozhkov
Copper Contributor
Oct 07, 2023
Solved

Dynamically filling formulas in an array

Hi! I dynamically create a product availability table from a list. Products can be added and removed from the list; the range of availability formulas needs to change with the size of the table. G2 ...
excel
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Oct 07, 2023

    bozhkov 

    =LET(cols,TOROW(UNIQUE(Tabelle1[color])),rows,UNIQUE(Tabelle1[car]&" "&Tabelle1[seller]),MAKEARRAY(COUNTA(rows),COUNTA(cols),LAMBDA(r,c,SUM((Tabelle1[car]&" "&Tabelle1[seller]=INDEX(rows,r))*(Tabelle1[color]=INDEX(cols,c))*Tabelle1[qty]))))

     

    Alternatively you can create a dynamic table and use the above formula. The name of the table in this example is Tabelle1.

     

    =TOROW(UNIQUE(Tabelle1[color]))

    This formula is in cell G1.

    =UNIQUE(Tabelle1[car]&" "&Tabelle1[seller])

    This formula is in cell F2.

     

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Oct 07, 2023

bozhkov 

Looks like I'm a bit late but I'll share my approach with MAKEARRAY which will create the header and the car/seller labels, too.

=LET(
    uniqueID, SORT(UNIQUE(Table1[car] & " " & Table1[seller])),
    colors, TOROW(SORT(UNIQUE(Table1[color]))),
    height, ROWS(uniqueID) + 1,
    width, COLUMNS(colors) + 1,
    GetCarQty, LAMBDA(r, c,
        LET(
            color, INDEX(colors, 1, c - 1),
            description, INDEX(uniqueID, r - 1),
            car, TEXTBEFORE(description, " "),
            seller, TEXTAFTER(description, " "),
            total, SUMIFS(
                Table1[quantity],
                Table1[car], car,
                Table1[seller], seller,
                Table1[color], color
            ),
            IF(
                AND(r = 1, c = 1),
                "",
                IF(AND(r = 1, c > 1), color, IF(AND(c = 1, r > 1), description, total))
            )
        )
    ),
    MAKEARRAY(height, width, GetCarQty)
)

 

Resources