Dynamically filling formulas in an array

[bozhkov]

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

[#2]

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

[#3 · Accepted solution]

@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.

[#4]

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

[#5]

@Patrick2788 @Hans Vogelaar 

I'm impressed! Many thanks for the help!