Oct 07 2023 05:49 AM
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;""))
Oct 07 2023 06:44 AM - edited Oct 07 2023 06:47 AM
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).
Oct 07 2023 07:13 AM
Solution=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.
Oct 07 2023 08:33 AM
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)
)
Oct 07 2023 09:18 AM
I'm impressed! Many thanks for the help!
Oct 07 2023 07:13 AM
Solution=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.