Problem Table1 contains the list of ingredients which can be used to prepare a dish. Problem Table2 contains the inventory of ingredients. List the ingredients against the dishes which are available to prepare the dishes on the basis of inventory going sequentially from A to D. Ingredients in result should be sorted. Ex. B contains p3 & p4 but p3 inventory is 1 and is already used by A. Hence, for B, only p4 is available.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 274
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Check ingredient availability for dishes with Power Query
Power Query solution 1 for Check ingredient availability for dishes, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = "Table" & x]}[Content],
B = Table.ExpandListColumn(
Table.TransformColumns(A("2"), {"Inventory", each {1 .. _}}),
"Inventory"
),
C = Table.AddIndexColumn(A("1"), "Id"),
D = Table.AddColumn(
C,
"Count",
each Table.RowCount(Table.SelectRows(C, (w) => w[Ingredient] = [Ingredient] and w[Id] <= [Id]))
),
E = Table.AddColumn(
D,
"Avail",
each Table.SelectRows(B, (z) => z[Ingredient] = [Ingredient] and z[Inventory] = [Count])[
Ingredient
]{0}?
),
F = Table.Group(E, "Dish", {"Ingedient", each Text.Combine(List.Sort([Avail]), ", ")})
in
FPower Query solution 2 for Check ingredient availability for dishes, proposed by Luan Rodrigues:
let
Fonte = Table.ExpandListColumn(
Table.TransformColumns(Tabela2, {"Inventory", each {1 .. _}}),
"Inventory"
),
grp = Table.Group(Tabela1, {"Ingredient"}, {{"Tab", each Table.AddIndexColumn(_, "Ind", 1)}}),
exp = Table.ExpandTableColumn(grp, "Tab", {"Dish", "Ind"}),
mesc = Table.Join(exp, {"Ingredient", "Ind"}, Fonte, {"Ingredient", "Inventory"}),
res = Table.Group(mesc, {"Dish"}, {{"Ingredient", each Text.Combine(_[Ingredient], ", ")}}),
srt = Table.Sort(res, {{"Dish", Order.Ascending}})
in
srtPower Query solution 3 for Check ingredient availability for dishes, proposed by Hussein SATOUR:
let
Source1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddIndex = Table.AddIndexColumn(Source1, "Index", 1, 1, Int64.Type),
AddCustom = Table.AddColumn(
AddIndex,
"Custom",
each
let
a = [Ingredient],
b = [Index],
c = Table.RowCount(Table.SelectRows(Table.FirstN(AddIndex, b), each [Ingredient] = a)),
d = List.Max(
Table.SelectRows(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
each [Ingredient] = a
)[Inventory]
)
in
if d >= c then "Y" else "N"
),
FilterRows = Table.SelectRows(AddCustom, each ([Custom] = "Y")),
GroupRows = Table.Group(FilterRows, {"Dish"}, {{"Ingredient", each List.Sort(_[Ingredient])}}),
ExtractVals = Table.TransformColumns(GroupRows, {"Ingredient", each Text.Combine(_, ",")})
in
ExtractValsPower Query solution 4 for Check ingredient availability for dishes, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
a = Table.AddColumn(t1, "Q", each t2[Inventory]{List.PositionOf(t2[Ingredient], [Ingredient])}),
b = Table.Group(a, "Ingredient", {"G", each _})[G],
c = Table.Combine(List.Transform(b, each Table.AddIndexColumn(_, "I", 1))),
d = Table.SelectRows(Table.AddColumn(c, "D", each [Q] - [I]), each [D] >= 0)[[Dish], [Ingredient]],
Sol = Table.Sort(
Table.Group(d, "Dish", {"Ingredient", each Text.Combine(List.Sort([Ingredient]), ", ")}),
{"Dish", 0}
)
in
SolPower Query solution 5 for Check ingredient availability for dishes, proposed by Eric Laforce:
let
fxSource = (n) => Excel.CurrentWorkbook(){[Name = n]}[Content],
tInv = Table.Buffer(fxSource("tData273_2")),
Source = fxSource("tData273_1"),
li = List.Buffer(Source[Ingredient]),
AddCpt = Table.AddColumn(
Table.AddIndexColumn(Source, "Idx"),
"Cpt",
(r) =>
[
i = r[Ingredient],
n = List.Count(List.Select(List.FirstN(li, r[Idx] + 1), each _ = i)),
inv = Table.SelectRows(tInv, each [Ingredient] = i)[Inventory]{0},
ok = (n <= inv)
][ok]
),
Filter = Table.SelectRows(AddCpt, each ([Cpt] = true))[[Dish], [Ingredient]],
Group = Table.Group(Filter, "Dish", {"Ingredient", each Text.Combine([Ingredient], ", ")})
in
GroupPower Query solution 6 for Check ingredient availability for dishes, proposed by Seokho MOON:
let
Tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Group = Table.Group(Tbl1, "Ingredient", {"Dish", each [Dish]}),
Join = Table.Join(Group, "Ingredient", Tbl2, "Ingredient"),
TblRepl = Table.ReplaceValue(
Join,
each [Dish],
each List.FirstN([Dish], [Inventory]),
Replacer.ReplaceValue,
{"Dish"}
)[[Dish], [Ingredient]],
Expand = Table.Sort(Table.ExpandListColumn(TblRepl, "Dish"), "Dish"),
Res = Table.Group(
Expand,
"Dish",
{"Ingredient", each Text.Combine(List.Sort([Ingredient]), ", ")},
0
)
in
ResPower Query solution 7 for Check ingredient availability for dishes, proposed by Antriksh Sharma:
let
Dishes = Table.Sort(
Table1,
{{"Dish", Order.Ascending}, {each Text.End([Ingredient], 1), Order.Ascending}}
),
Inventory = Table.PrefixColumns(Table2, "T2"),
Acc = List.Accumulate(
List.Distinct(Dishes[Dish]),
[T1 = {Dishes}, T2 = {Inventory}, S = {}],
(state, current) => [
a = Table.SelectRows(List.First(state[T1]), (x) => x[Dish] = current),
b = Table.Join(a, "Ingredient", List.Last(state[T2]), "T2.Ingredient", JoinKind.RightOuter),
c = Table.FromRows(
Table.ToList(
b,
each List.FirstN(_, 3)
& {if List.First(_) = current then List.Last(_) - 1 else List.Last(_)}
),
Table.ColumnNames(b)
),
d = Text.Combine(List.RemoveNulls(c[Ingredient]), ", "),
e = Table.SelectRows(c[[T2.Ingredient], [T2.Inventory]], each [T2.Inventory] > 0),
T1 = state[T1] & {a},
T2 = state[T2] & {e},
S = state[S] & {d}
]
),
Result = Table.FromColumns(
{List.Distinct(Dishes[Dish])} & {Acc[S]},
type table [Dish = text, Ingredients = text]
)
in
ResultPower Query solution 8 for Check ingredient availability for dishes, proposed by Peter Krkos:
let
Table1 = Expression.Evaluate("Table.FromRows(List.Transform(Text.Split(""A;p1|A;p3|A;p2|B;p4|B;p3|C;p1|C;p2|D;p1|D;p5|D;p4|D;p2"",""|""), each Text.Split(_, "";"")), {""Dish"",""Ingredient""})", hashtag#shared),
Table2 = Table.TransformColumnTypes(Expression.Evaluate("Table.FromRows(List.Transform(Text.Split(""p1;1|p2;2|p3;1|p4;2|p5;4"",""|""), each Text.Split(_, "";"")), {""Ingredient"",""Inventory""})", hashtag#shared), {{"Inventory", Int64.Type}}),
T2R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(Table2))),
Values = List.Accumulate(Table1[Ingredient], {{}, T2R}, (s,c)=>
let curValue = Record.Field(s{1}, c) in { s{0} & {if curValue > 0 then c else null}, s{1} & Record.AddField([], c, curValue-1) }),
Merged = Table.FromColumns({Table1[Dish]} & {Values{0}}, type table[Dish=text, Ingredient=text]),
GroupedRows = Table.Group(Merged, {"Dish"}, {{"Ingredient", each Text.Combine(List.Sort([Ingredient]), ", "), type text}})
in
GroupedRows
Power Query solution 9 for Check ingredient availability for dishes, proposed by Alexandre Garcia:
let
A = each Excel.CurrentWorkbook(){[Name = "Table" & _]}[Content],
B = (
(v) =>
List.Generate(
() => 0,
each _ < List.Count(v),
each _ + 1,
each
if List.Select(Table.ToRows(A("2")), (x) => x{0} = v{_}{1}){0}{1}
< List.Count(List.Select(List.Range(v, 0, _ + 1), (x) => x{1} = v{_}{1}))
then
{v{_}{0}, null}
else
v{_}
)
)(Table.ToRows(A("1"))),
C = Table.FromList(
List.Distinct(A("1")[Dish]),
(x) => {x, Text.Combine(List.Sort(List.Zip(List.Select(B, each _{0} = x)){1}), ", ")},
Value.Type(A("1"))
)
in
CPower Query solution 10 for Check ingredient availability for dishes, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart1"]}[Content],
firstGrouping = Table.TransformColumns(
Table.Group(source, {"Ingredient"}, {{"Rows", each _, type table}}),
{{"Rows", each Table.AddIndexColumn(_, "ID", 1, 1)}}
)[[Rows]],
expandRows = Table.ExpandTableColumn(firstGrouping, "Rows", {"Dish", "Ingredient", "ID"}),
mergeWStock = Table.NestedJoin(
expandRows,
{"Ingredient"},
tblStock,
{"Ingredient"},
"tblStock",
JoinKind.LeftOuter
),
expandStock = Table.ExpandTableColumn(mergeWStock, "tblStock", {"Inventory"}, {"Inventory"}),
selectRows = Table.SelectRows(expandStock, each [ID] <= [Inventory]),
secondGrouping = Table.Sort(
Table.Group(
selectRows,
{"Dish"},
{{"Ingredient", each Text.Combine([Ingredient], ", "), type text}}
),
{"Dish", Order.Ascending}
)
in
secondGroupingPower Query solution 11 for Check ingredient availability for dishes, proposed by Fredson Alves Pinho:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
join = Table.ExpandTableColumn(
Table.NestedJoin(Fonte, {"Ingredient"}, Table2, {"Ingredient"}, "Table2"),
"Table2",
{"Inventory"}
),
Index = Table.AddIndexColumn(
Table.Sort(join, {{"Dish", Order.Ascending}, {"Ingredient", Order.Ascending}}),
"Index"
),
fn = (i, p) => Table.RowCount(Table.SelectRows(Index, each [Index] < i and [Ingredient] = p)),
filter = Table.SelectRows(Index, each [Inventory] - fn([Index], [Ingredient]) > 0)[
[Dish],
[Ingredient]
],
group = Table.Group(
filter,
{"Dish"},
{{"Ingredient", each Text.Combine(_[Ingredient], ", "), type text}}
)
in
groupPower Query solution 12 for Check ingredient availability for dishes, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = each [
a = Table.SelectRows(Table2, (x) => List.Max(_[Ingredient]) = x[Ingredient])[Inventory]{0},
b = List.Count(_[Dish]),
c = List.FirstN(_[Dish], if a < b then a else b)
][c],
Grp = Table.ExpandListColumn(Table.Group(Source, {"Ingredient"}, {{"Dish", Fx}}), "Dish"),
Res = Table.Sort(
Table.Group(Grp, {"Dish"}, {{"Ingedient", each Text.Combine(_[Ingredient], ", ")}}),
"Dish"
)
in
ResSolving the challenge of Check ingredient availability for dishes with Excel
Excel solution 1 for Check ingredient availability for dishes, proposed by Bo Rydobon 🇹🇭:
=LET(d,
A2:A12,
i,
B2:B12,
j,
D2:D6,
u,
UNIQUE(
d
),
HSTACK(VSTACK(
A1,
u
),
REDUCE(B1,
u,
LAMBDA(a,
v,
VSTACK(a,
TEXTJOIN(", ",
,
SORT(FILTER(i,
(d=v)*(XLOOKUP(
i,
j,
E2:E6-BYROW(
N(
j=TEXTSPLIT(
ARRAYTOTEXT(
a
),
", "
)
),
SUM
)
))))))))))
=GROUPBY(
A1:A12,
B1:B12,
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
x
)
)
),
3,
0,
,
MAP(
B2:B12,
LAMBDA(
x,
LOOKUP(
x,
D2:E6
)>=COUNTIF(
B2:x,
x
)
)
)
)Excel solution 2 for Check ingredient availability for dishes, proposed by 🇰🇷 Taeyong Shin:
=LET(d,
A2:A12,
r,
D2:D6,
DROP(REDUCE(HSTACK(
0,
LAMBDA(
E2:E6
)
),
UNIQUE(
d
),
LAMBDA(a,
v,
LET(i,
(@TAKE(
a,
-1,
-1
))(),
p,
FILTER(
r,
i
),
x,
XLOOKUP(
FILTER(
B2:B12,
d=v
),
p,
p,
""
),
VSTACK(
a,
HSTACK(
v,
TEXTJOIN(
", ",
,
SORT(
x
)
),
LAMBDA(
i-ISNUMBER(
XMATCH(
r,
x
)
)
)
)
)))),
1,
-1))Excel solution 3 for Check ingredient availability for dishes, proposed by Kris Jaganah:
=LET(
a,
SORT(
TEXTSPLIT(
CONCAT(
REPT(
D2:D6&",",
E2:E6
)
),
,
",",
1
)
),
b,
a&"-"&SEQUENCE(
ROWS(
a
)
)-XMATCH(
a,
a
)+1,
c,
B2:B12,
d,
c&"-"&MAP(
c,
LAMBDA(
x,
SUM(
N(
B2:x=x
)
)
)
),
e,
XLOOKUP(
d,
b,
a,
""
),
VSTACK(
A1:B1,
GROUPBY(
A2:A12,
e,
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
x
)
)
),
,
0
)
)
)Excel solution 4 for Check ingredient availability for dishes, proposed by Oscar Mendez Roca Farell:
=LET(
s,
SEQUENCE(
,
MAX(
E2:E6
)
),
m,
MAP(
B2:B12,
LAMBDA(
b,
b&-COUNTIF(
B2:b,
b
)
)
),
t,
TOCOL(
IFS(
E2:E6>=s,
D2:D6&-s
),
2
),
DROP(
GROUPBY(
XLOOKUP(
t,
m,
A2:A12
),
MID(
t,
1,
2
),
ARRAYTOTEXT,
,
0
),
-1
)
)Excel solution 5 for Check ingredient availability for dishes, proposed by Duy Tùng:
=GROUPBY(
A1:A12,
B1:B12,
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
x
)
)
),
3,
0,
,
LOOKUP(
B2:B12,
D2:E6
)-MAP(
B2:B12,
LAMBDA(
v,
SUM(
N(
B2:v=v
)
)
)
)>=0
)_x000D_
Excel solution 6 for Check ingredient availability for dishes, proposed by LEONARD OCHEA 🇷🇴:
=LET(i,
B2:B12,
GROUPBY(A2:A12,
REDUCE("",
UNIQUE(
i
),
LAMBDA(a,
h,
a&MAP(i,
LAMBDA(v,
IF((COUNTIF(
B2:v,
h
)<=XLOOKUP(
h,
D2:D6,
E2:E6
))*(v=h),
h,
""))))),
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
x
)
)
),
,
0))_x000D_
_x000D_
Excel solution 7 for Check ingredient availability for dishes, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A12,
v,
B2:B12,
p,
D2:D6,
q,
E2:E6,
hdr,
A1:B1,
a,
XLOOKUP(
v,
p,
q
),
b,
MAP(
v,
SEQUENCE(
ROWS(
v
)
),
LAMBDA(
x,
y,
SUM(
ABS(
TAKE(
v,
y
)=x
)
)
)
),
c,
FILTER(
HSTACK(
u,
v
),
a>=b
),
d,
SORT(
c,
{1,
2},
{1,
1}
),
e,
GROUPBY(
TAKE(
d,
,
1
),
DROP(
d,
,
1
),
ARRAYTOTEXT,
0,
0
),
VSTACK(
hdr,
e
)
)
_x000D_
_x000D_
Excel solution 8 for Check ingredient availability for dishes, proposed by Pieter de B.:
=GROUPBY(
A2:A12,
MAP(
B2:B12,
LAMBDA(
b,
LET(
c,
SUM(
N(
B2:b=b
)
),
IF(
c<=LOOKUP(
b,
D2:E6
),
b,
""
)
)
)
),
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
x
)
)
),
,
0
)
_x000D_
_x000D_
Excel solution 9 for Check ingredient availability for dishes, proposed by Hamidi Hamid:
=LET(
aa,
A2:A12,
bb,
B2:B12,
f,
LAMBDA(
k,
DROP(
REDUCE(
0,
k,
LAMBDA(
a,
b,
IFNA(
VSTACK(
a,
TEXTSPLIT(
b,
", ",
)
),
""
)
)
),
1
)
),
x,
GROUPBY(
aa,
bb,
COUNTA,
,
0
),
y,
DROP(
GROUPBY(
aa,
bb,
ARRAYTOTEXT,
,
0
),
,
1
),
b,
MAP(
bb,
LAMBDA(
a,
COUNTIF(
B2:a,
a
)
)
),
g,
DROP(
GROUPBY(
aa,
b,
ARRAYTOTEXT,
,
0
),
,
1
),
rg,
f(
g
),
ry,
f(
y
),
h,
IFERROR(
IF(
XLOOKUP(
ry,
D2:D6,
E2:E6,
0
)-rg<0,
"",
ry
),
""
),
HSTACK(
UNIQUE(
A2:A12
),
BYROW(
h,
LAMBDA(
a,
TEXTJOIN(
", ",
1,
SORT(
a,
1,
1,
1
)
)
)
)
)
)
_x000D_
_x000D_
Excel solution 10 for Check ingredient availability for dishes, proposed by Asheesh Pahwa:
=LET(
m,
MAP(
D2:D6,
E2:E6,
LAMBDA(
x,
y,
LET(
f,
FILTER(
A2:A12,
B2:B12=x
),
ARRAYTOTEXT(
TAKE(
f,
y
)
)
)
)
),
u,
UNIQUE(
A2:A12
),
HSTACK(
u,
MAP(
u,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
D2:D6,
ISNUMBER(
SEARCH(
x,
m
)
)
)
)
)
)
)
)
_x000D_
_x000D_
Excel solution 11 for Check ingredient availability for dishes, proposed by ferhat CK:
=LET(a,
REDUCE({"Ingredient",
"Inventory"},
D2:D6,
LAMBDA(x,
y,
VSTACK(x,
LET(f,
FILTER(
A2:B12,
B2:B12=y
),
HSTACK(f,
SCAN(XLOOKUP(
y,
D2:D6,
E2:E6
),
(-1)*SEQUENCE(
ROWS(
f
)
)^0,
SUM)))))),
GROUPBY(
TAKE(
a,
,
1
),
CHOOSECOLS(
a,
2
),
ARRAYTOTEXT,
3,
0,
,
TAKE(
a,
,
-1
)>-1
))
_x000D_
_x000D_
Excel solution 12 for Check ingredient availability for dishes, proposed by Jaroslaw Kujawa:
=LET(
b;
A2:B12;
c;
DROP(
REDUCE(
"";
B2:B12;
LAMBDA(
a;
x;
VSTACK(
a;
XLOOKUP(
x;
$D$2:$D$6;
$E$2:$E$6
)-COUNTIF(
$B$2:x;
x
)
)
)
);
1
);
d;
HSTACK(
b;
c
);
e;
DROP(
FILTER(
d;
TAKE(
d;
;
-1
)>=0
);
;
-1
);
GROUPBY(
TAKE(
e;
;
1
);
TAKE(
e;
;
-1
);
ARRAYTOTEXT;
;
0
)
)
_x000D_
_x000D_
Excel solution 13 for Check ingredient availability for dishes, proposed by Gerson Pineda:
=LET(
i,
B2:B12,
GROUPBY(
A2:A12,
i,
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
x
)
)
),
,
0,
,
XLOOKUP(
i,
D2:D6,
E2:E6
)>=MAP(
i,
LAMBDA(
x,
COUNTIF(
B2:x,
x
)
)
)
)
)
_x000D_
_x000D_
Excel solution 14 for Check ingredient availability for dishes, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
m,
B1:B12,
GROUPBY(
A1:A12,
m,
LAMBDA(
x,
ARRAYTOTEXT(
SORT(
x
)
)
),
3,
0,
,
MAP(
m,
LAMBDA(
x,
COUNTIF(
B2:x,
x
)
)
)<=LOOKUP(
m,
D2:E6
)
)
)
_x000D_
_x000D_
Excel solution 15 for Check ingredient availability for dishes, proposed by Ricardo Romero Garcia:
=LET(
e;
ELEGIRCOLS;
f;
FILTRAR;
m;
D2:D6;
x;
REDUCE(
A1:C1;
m;
LAMBDA(
aa;
vv;
APILARV(
LET(
b;
f(
A2:B12;
B2:B12=vv
);
d;
f(
E2:E6;
m=vv
);
c;
SCAN(
0;
SECUENCIA(
FILAS(
b
)
);
LAMBDA(
a;
v;
SI(
v<=d;
1
)
)
);
f(
APILARH(
b;
c
);
c
)
);
aa
)
)
);
y;
ORDENARPOR(
x;
e(
--x;
3
);
;
e(
x;
1
);
;
e(
x;
2
);
);
AGRUPARPOR(
e(
y;
1
);
e(
y;
2
);
MATRIZATEXTO;
3;
0
)
)
_x000D_
Solving the challenge of Check ingredient availability for dishes with Python
_x000D_
Python solution 1 for Check ingredient availability for dishes, proposed by Luan Rodrigues:
import pandas as pd
file = r"PQ_Challenge_274.xlsx"
df1 = pd.read_excel(file,usecols="A:B").dropna()
df2 = pd.read_excel(file,usecols="D:E", nrows=5)
df2 = (df2.assign(Inventory=df2['Inventory'].apply(lambda x: list(range(1,x+1))))
.explode('Inventory'))
df1['Ind'] = df1.groupby(['Ingredient']).cumcount()+1
mrg = df1.merge(
df2,
left_on=["Ingredient", "Ind"],
right_on=["Ingredient.1", "Inventory"],
how="inner"
)
rst = mrg.groupby('Dish')['Ingredient'].agg(','.join).reset_index()
print(rst)
_x000D_
Solving the challenge of Check ingredient availability for dishes with Python in Excel
_x000D_
Python in Excel solution 1 for Check ingredient availability for dishes, proposed by Alejandro Campos:
df1, df2 = xl("A1:B12", headers=True), xl("D1:E6", headers=True)
inventory = {k: v for k, v in zip(df2["Ingredient"], df2["Inventory"])}
result = [{"Dish": d, "Ingredients": ", ".join(i for i in sorted(df1[df1["Dish"] == d]["Ingredient"])
if inventory.get(i, 0) and not (inventory.update({i: inventory[i] - 1}) or 0))} for d in df1["Dish"].unique()]
result_df = pd.DataFrame(result)
_x000D_
_x000D_
Python in Excel solution 2 for Check ingredient availability for dishes, proposed by Francesco Bianchi 🇮🇹:
Hello, my Python in Excel solution:
df1 = xl("Sheet1!$A$1:$B$12", headers=True)
df2 =xl("Sheet1!$D$1:$E$6", headers=True)
df = pd.merge(df1, df2, left_on='Ingredient', right_on='Ingredient')
df = df[df['Inventory'] >= df.groupby('Ingredient')['Ingredient'].cumcount()+1]
df.groupby('Dish')['Ingredient'].apply(lambda x: ', '.join(sorted(', '.join(x).split(', ')))).reset_index()
_x000D_
&&