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 =LET(qty; SUMPRODUCT(($A$2:$A$172&" "&$C$2:$C$172&$B$2:$B$172=$F2&G$1)*$D$2:$D$172); IF(qty>0;qty;""))

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.

Dynamically filling formulas in an array

4 Replies

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)
)

Cars 10.7.xlsx26 KB

bozhkov
Copper Contributor
Oct 07, 2023
Patrick2788 HansVogelaar
I'm impressed! Many thanks for the help!

OliverScheurich
Gold Contributor
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.
HansVogelaar
MVP
Oct 07, 2023
bozhkov

In G2:

=IF(OR($F2="", G$1=""), "", LET(qty, SUMPRODUCT(($A$2:$A$1000&" "&$C$2:$C$1000=$F2)*($B$2:$B$1000=G$1), $D$2:$D$1000), IF(qty>0, qty, "")))

Fill down to for example row 100, then to the right to column CZ (or vice versa).

Forum Discussion

Dynamically filling formulas in an array

4 Replies

Resources