Home » Max Items Within Budget

Max Items Within Budget

Provide a formula to list the maximum number of items (one unit only of each item) which can be bought in $150 from A2:A10. Retain the original sort order of items in answer

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 180
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Max Items Within Budget with Power Query

Power Query solution 1 for Max Items Within Budget, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Acc = 
    let
      c = Table.ToColumns(Table.Sort(Source, {"Cost ($)"}))
    in
      Table.FromColumns(c & {List.Accumulate(c{1}, {}, (s, l) => s & {List.Last({0} & s) + l})}), 
  Sort = List.Sort(
    Table.SelectRows(Acc, each [Column3] <= 150)[Column1], 
    each List.PositionOf(Source[Items], _)
  )
in
  Sort
Power Query solution 2 for Max Items Within Budget, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Max = 150, 
  AddSort = 
    let
      r = Table.ToRecords(Source)
    in
      List.Accumulate(List.Positions(r), {}, (s, d) => s & {Record.AddField(r{d}, "Sort", d)}), 
  SortingRecords = List.Sort(
    AddSort, 
    {{each _[#"Cost ($)"], Order.Ascending}, {each _[Sort], Order.Ascending}}
  ), 
  Calculate = List.Accumulate(
    SortingRecords, 
    {}, 
    (s, d) =>
      let
        c  = d[#"Cost ($)"], 
        rc = List.Sum(List.Transform(s, each _[#"Cost ($)"]))
      in
        if s = {} and c > Max then
          s
        else if s = {} and c < Max then
          s & {d}
        else if rc + c > Max then
          s
        else
          s & {d}
  ), 
  Solution = List.Transform(List.Sort(Calculate, {each _[Sort], Order.Ascending}), each _[Items])
in
  Solution
Power Query solution 3 for Max Items Within Budget, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sort = Table.Sort(Source, {{"Cost ($)", Order.Ascending}}), 
  Add = Table.AddIndexColumn(Sort, "Index", 1, 1, Int64.Type), 
  RT = Table.SelectRows(
    Table.AddColumn(Add, "Sum", each List.Sum(List.FirstN(Add[#"Cost ($)"], [Index]))), 
    each [Sum] <= 150
  )[Items], 
  Sol = List.Sort(RT, each List.PositionOf(Source[Items], _))
in
  Sol
Power Query solution 4 for Max Items Within Budget, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  class = Table.Sort(Fonte, {{"Cost ($)", Order.Ascending}}), 
  Ind = Table.AddIndexColumn(class, "Índice", 1, 1, Int64.Type), 
  acc = Table.AddColumn(Ind, "Personalizar", each List.Sum(List.FirstN(Ind[#"Cost ($)"], [Índice]))), 
  fil = Table.SelectRows(acc, each [Personalizar] <= 150), 
  res = Table.Sort(fil, each List.PositionOf(Fonte[Items], [Items]))[[Items]]
in
  res
Power Query solution 5 for Max Items Within Budget, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddOrigSort = Table.AddIndexColumn(Source, "OrigSort", 1, 1, Int64.Type), 
  Sort = Table.Sort(AddOrigSort, {{"Cost ($)", Order.Ascending}}), 
  AddIndex = Table.AddIndexColumn(Sort, "Index", 1, 1, Int64.Type), 
  AddCumulSum = Table.AddColumn(
    AddIndex, 
    "CumulSum", 
    each [a = List.FirstN(AddIndex[#"Cost ($)"], [Index]), b = List.Sum(a)][b]
  ), 
  Filter = Table.SelectRows(AddCumulSum, each [CumulSum] <= 150), 
  Sort2 = Table.Sort(Filter, {{"OrigSort", Order.Ascending}}), 
  Clean = Table.SelectColumns(Sort2, {"Items"})
in
  Clean
Power Query solution 6 for Max Items Within Budget, proposed by Victor Wang: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  sortByCost = Table.Sort(Source, {{"Cost ($)", Order.Ascending}}), 
  getPos = List.Accumulate(
    sortByCost[#"Cost ($)"], 
    [pos = 0, sum = 0], 
    (state, current) =>
      if state[sum] + current > 150 then
        state
      else
        [pos = state[pos] + 1, sum = List.Sum({state[sum], current})]
  ), 
  getList = List.Sort(
    List.FirstN(sortByCost[Items], getPos[pos]), 
    each List.PositionOf(Source[Items], _)
  )
in
  getList
Power Query solution 7 for Max Items Within Budget, proposed by Guillermo Arroyo: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a      = Table.AddIndexColumn(Origen, "Aux", 1), 
  b      = Table.Sort(a, {{"Cost ($)", Order.Ascending}}), 
  c      = Table.AddIndexColumn(b, "Aux1", 1), 
  d      = Table.AddColumn(c, "Accumulate", each List.Sum(List.FirstN(c[#"Cost ($)"], [Aux1]))), 
  e      = Table.SelectRows(d, each [Accumulate] <= 150), 
  f      = Table.Sort(e, {{"Aux", Order.Ascending}}), 
  g      = Table.SelectColumns(f, {"Items"})
in
  g
Power Query solution 8 for Max Items Within Budget, proposed by Tulio Marcos: 
let
  Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content], 
  #"1" = Table.TransformColumnTypes(Fonte, {{"Cost ($)", Int64.Type}}), 
  #"2" = Table.AddIndexColumn(#"1", "Class", 0, 1, Int64.Type), 
  #"3" = Table.Sort(#"2", {{"Cost ($)", Order.Ascending}}), 
  #"4" = Table.AddIndexColumn(#"3", "Índice", 1, 1, Int64.Type), 
  #"5" = Table.AddColumn(
    #"4", 
    "Personalizar", 
    each List.Sum(List.FirstN(#"4"[#"Cost ($)"], [Índice]))
  ), 
  #"6" = Table.SelectRows(#"5", each [Personalizar] < 150), 
  #"7" = Table.Sort(#"6", {{"Class", Order.Ascending}})
in
  #"7"

Solving the challenge of Max Items Within Budget with Excel

Excel solution 1 for Max Items Within Budget, proposed by Bo Rydobon 🇹🇭: 
=LET(z,A2:A10,c,B2:B10,f,FILTER(SORTBY(z,c),SCAN(,SORT(c),LAMBDA(a,v,a+v))<=150),SORTBY(f,XMATCH(f,z)))
=LET(c,B2:B10,d,c+ROW(c)%,FILTER(A2:A10,d<=SMALL(d,XMATCH(150,SCAN(,SORT(c),LAMBDA(a,v,a+v)),-1))))
Excel solution 2 for Max Items Within Budget, proposed by Rick Rothstein: 
=LET(r,A2:A10,s,SORT(HSTACK(r,SEQUENCE(ROWS(r)),B2:B10),3),TAKE(SORT(FILTER(s,SCAN(,TAKE(s,,-1),LAMBDA(a,x,a+x))<151),2),,1))
Excel solution 3 for Max Items Within Budget, proposed by John V.: 
=LET(c,B2:B10,v,c+ROW(c)%,p,XMATCH(v,SORT(v)),FILTER(A2:A10,MMULT((TOROW(p)<=p)*TOROW(c),p^0)<=150))
Excel solution 4 for Max Items Within Budget, proposed by محمد حلمي: 
=TOCOL(IF(XMATCH(A2:A10,FILTER(TAKE(SORT(A2:B10,2),,1),SCAN(0,SORT(B2:B10),LAMBDA(a,d,a+d))<151)),A2:A10),2)
Excel solution 5 for Max Items Within Budget, proposed by محمد حلمي: 
=FILTER(A2:A10,150/B2:B10>2)
Excel solution 6 for Max Items Within Budget, proposed by 🇰🇷 Taeyong Shin: 
=LET(
 d, SORT(A2:B10, 2),
 v, MAKEARRAY(ROWS(d), 1, LAMBDA(r,c, IF(SUM(TAKE(d, r)) <= 150, INDEX(d, r, 1), x))),
 TOCOL(SORTBY(v, XMATCH(v, A2:A10)), 2)
)
Excel solution 7 for Max Items Within Budget, proposed by Kris Jaganah: 
=LET(a,A2:A10,c,SEQUENCE(ROWS(a))&"-"&a,d,SORT(HSTACK(c,B2:B10),2,1),TEXTAFTER(SORT(FILTER(TAKE(d,,1),SCAN(0,TAKE(d,,-1),LAMBDA(x,y,x+y))<150)),"-"))
Excel solution 8 for Max Items Within Budget, proposed by Julian Poeltl: 
=LET(T,A2:B10,I,TAKE(T,,1),S,SORT(T,2),R,SCAN(,TAKE(S,,-1),LAMBDA(A,B,A+B)),F,FILTER(TAKE(S,,1),R<=150),FILTER(I,ISNUMBER(XMATCH(I,F))))
Excel solution 9 for Max Items Within Budget, proposed by Timothée BLIOT: 
=LET(A,A2:B10,B,SORT(A,2),C,FILTER(INDEX(B,,1),SCAN(0,INDEX(B,,2),LAMBDA(a,v,a+v))<=150), D, MAP(INDEX(A,,1),LAMBDA(x, LET(W,IF(ISNUMBER(XMATCH(x,C)),x,0), W<>0) )), FILTER(INDEX(A,,1),D))
Excel solution 10 for Max Items Within Budget, proposed by Luan Rodrigues: 
= SUM(Tabela1[Cost ($)])
/*-------------------------------------------*/
Total Cost  = 
var sum2 = [Soma]
var tab = 
SUMMARIZE(
 ALLSELECTED(Tabela1[Items]),
 Tabela1[Items],
 "cost",[Soma])
var vacc = 
SUMX(
 FILTER(
 tab,
 [cost]<=sum2),[Soma])
return
CALCULATE(
[Soma],
FILTER(
 ALLSELECTED(Tabela1[Items]),
 vacc <=150))
Excel solution 11 for Max Items Within Budget, proposed by Hussein SATOUR: 
=LET(a, SORT(HSTACK(A2:B10,SEQUENCE(COUNT(B2:B10))),2), INDEX(SORT(FILTER(a, SCAN(0,INDEX(a,,2),LAMBDA(x,y,x+y))<150),3),,1))
Excel solution 12 for Max Items Within Budget, proposed by Oscar Mendez Roca Farell: 
=LET(_a, A2:A10,_m, ORDER(A2:B10,2), INDEX(_a, ORDER( MATCH( DROP( TAKE(_m, COUNT( SCAN(0, INDEX(_m, ,2), LAMBDA(i, x, i+x/((i+x)<150))))), ,-1),_a, ))))
Excel solution 13 for Max Items Within Budget, proposed by Sunny Baggu: 
=LET(_input,A2:B10,_tbl,HSTACK(SEQUENCE(ROWS(_input)),_input),
_tbls,SORT(_tbl,3),
_cond,SCAN(0,TAKE(_tbls,,-1),LAMBDA(a,v,a+v))<=150,DROP(SORT(FILTER(DROP(_tbls,,-1),_cond)),,1))
Excel solution 14 for Max Items Within Budget, proposed by Sunny Baggu: 
=LET(_tbl,HSTACK(SEQUENCE(ROWS(A2:A10))&A2:A10,B2:B10),
_tblsort,SORT(_tbl,2,),_rtot,SCAN(0,DROP(_tblsort,,1),LAMBDA(a,v,a+v)),
MID(SORT(TOCOL(TAKE(IFS(_rtot<=150,_tblsort),,1),3)),2,100))
Excel solution 15 for Max Items Within Budget, proposed by Md. Zohurul Islam: 
=LET(z,A2:B10,
a,SORT(HSTACK(z,SEQUENCE(ROWS(z))),2,1),
b,SCAN(0,CHOOSECOLS(a,2),SUM),
d,TAKE(SORT(FILTER(a,b<=150),3,1),,1),
d)
Excel solution 16 for Max Items Within Budget, proposed by Charles Roldan: 
=LET(Items, A2:A10, Cost, B2:B10, 
Budget, 150, Add, LAMBDA(a,b, a + b), 
Buy, FILTER(SORTBY(Items, Cost),
 SCAN(, SORT(Cost), Add) <= Budget), 
SORTBY(Buy, XMATCH(Buy, Items)))
Excel solution 17 for Max Items Within Budget, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert: 
=MAP(LET(w;SORT(B2:B10;;1);q;150-SCAN(0;w;LAMBDA(x;y;x+y));UNIQUE(XLOOKUP(FILTER(q;MIN(q)*(q>0));q;w)));LAMBDA(z;TEXTJOIN(", ";;IFERROR(MAP(B2:B10;A2:A10;LAMBDA(a;b;XLOOKUP(z;a;b)));""))))
Excel solution 18 for Max Items Within Budget, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=LET(a,A2:A10,b,B2:B10,c,SCAN(,SORT(b,1,1),LAMBDA(x,y,x+y)),FILTER(a,XLOOKUP(a,SORTBY(a,b,1),c)<=150))
Excel solution 19 for Max Items Within Budget, proposed by Nicolas Micot: 
=LET(_tableau;ASSEMB.H(SEQUENCE(NBVAL(A2:A10));A2:B10);_tri;TRIER(_tableau;3;1);_couts;INDEX(_tri;0;3);_coutCumule;SCAN(0;_couts;LAMBDA(a;b;a+b));_filtre;FILTRE(_tri;_coutCumule<=150);INDEX(TRIER(_filtre;1;1);0;2))
Excel solution 20 for Max Items Within Budget, proposed by Guillermo Arroyo: 
=LET(m,A2:B10,p,SORT(HSTACK(m,SEQUENCE(ROWS(m))),2),TAKE(SORT(FILTER(p,SCAN(0,INDEX(p,0,2),LAMBDA(i,j,i+j))<=150),3),,1))
Excel solution 21 for Max Items Within Budget, proposed by Quadri Olayinka Atharu: 
=LET(s,SORT(A2:B10,2),rt,SCAN(0,TAKE(s,,-1),LAMBDA(x,y,x+y))<=150,
a,FILTER(s,rt),b,HSTACK(a,XMATCH(a,A2:A10)),r,TAKE(SORT(b,3),,1),
r)
Excel solution 22 for Max Items Within Budget, proposed by Rayan S.: 
=LET(
 t, SORT(A2:B10, 2, 1),
 n, TAKE(t, , -1),
 s, SCAN(0, n, LAMBDA(a, b, a + b)),
 c, COUNT(FILTER(s, s < 151)),
 TAKE(t, c, 1)
)
Excel solution 23 for Max Items Within Budget, proposed by Gabriel Raigosa: 
=FILTER(A2:A10,ISNUMBER(XMATCH(A2:A10,LET(m,SORT(A2:B10,2,1,),IF(SCAN(,TAKE(m,,-1),LAMBDA(up,dn,up+dn))<=150,TAKE(m,,1),))))) 
 =LET(it,A2:A10,co,B2:B10,az,SORTBY(it,co),FILTER(it,ISNUMBER(XMATCH(it,IF(SCAN(,XLOOKUP(az,it,co),LAMBDA(up,dn,up+dn))<=150,az,))))) 
▶️ ES:
=FILTRAR(A2:A10,ESNUMERO(COINCIDIRX(A2:A10,LET(m,ORDENAR(A2:B10,2,1,),SI(SCAN(,TOMAR(m,,-1),LAMBDA(up,dn,up+dn))<=150,TOMAR(m,,1),))))) 
=LET(it,A2:A10,co,B2:B10,az,ORDENARPOR(it,co),FILTRAR(it,ESNUMERO(COINCIDIRX(it,SI(SCAN(,BUSCARX(az,it,co),LAMBDA(up,dn,up+dn))<=150,az,)))))
Excel solution 24 for Max Items Within Budget, proposed by Miguel Angel Franco García: 
=LET(a;SI(B2:B10<=PROMEDIO(B2:B10)+1; A2:A10;""); FILTRAR(a;a<>""))
Excel solution 25 for Max Items Within Budget, proposed by Hussain Ali Nasser: 
=LET(_range,A2:B10,_limit,150,_sortedlist,SORT(_range,2,1),_cumulativec&osts,SCAN(0,TAKE(_sortedlist,,-1),LAMBDA(_start,_costs,_start+_costs)),_countcosts,COUNT(FILTER(_cumulativecosts,_cumulativecosts<=_limit)),TAKE(_sortedlist,_countcosts,1))
Excel solution 26 for Max Items Within Budget, proposed by Stevenson Yu: 
=LET(A, A2:B10, LIMIT,150,
B, SORT(A,2),
C, DROP(VLOOKUP(A,FILTER(B,SCAN(0,DROP(B,,1),LAMBDA(X,Y,X+Y))<=LIMIT),1,0),,-1),
D, FILTER(C,ISTEXT(C)),
IFERROR(D,"None"))
Excel solution 27 for Max Items Within Budget, proposed by Al Jun Esguerra: 
=SORT(A2:B10,2,1)
Step 2: Column K is running sum
 =IF(I2<>"",SUM($J$2:J2),"")
Step 3: Input on cell L2 = 150
Step 4: Column F
 =SORT(FILTER(H2:H10,K2:K10<$L$2))
Step 5: Column D based on rownumber on Column F
 =IF(F2<>"",INDEX(A:A,F2),"")

&&

Leave a Reply