Find out unique combination of numbers from column A containing N numbers given in column B which sums to column C. For example, for row 2, N is 3 which means we need to find combinations of 3 numbers from 1, 4, 0, 3, 5, 2 having sum of 6.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 284
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Unique Combinations Matching Sum with Power Query
Power Query solution 1 for Unique Combinations Matching Sum, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Custom",
each Text.Combine(
List.Distinct(
List.Transform(
List.Accumulate(
Text.Split([Numbers], ", "),
{""},
(s, l) => s & List.Transform(s, each _ & ", " & l)
),
(c) =>
let
x = Text.Middle(c, 2, 99),
t = Text.Split(x, ", ")
in
if List.Count(t) = [N] and List.Sum(List.Transform(t, Number.From)) = [Sum] then
"(" & x & ")"
else
null
)
),
", "
)
)
in
AnsPower Query solution 2 for Unique Combinations Matching Sum, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
n = Text.Split([Numbers], ", "),
c = List.Count(n)
in
Text.Combine(
List.Transform(
List.Distinct(
List.Select(
List.Transform(
{0 .. Number.Power(2, c) - 1},
each List.Accumulate(
{0 .. c - 1},
{},
(s, c) =>
if Number.Mod(Number.IntegerDivide(_, Number.Power(2, c)), 2) = 1 then
s & {Number.From(n{c})}
else
s
)
),
(l) => List.Count(l) = [N] and List.Sum(l) = [Sum]
)
),
each "(" & Text.Combine(List.Transform(_, Text.From), ", ") & ")"
),
", "
)
)
in
SPower Query solution 3 for Unique Combinations Matching Sum, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(x) =>
let
a = List.Transform(Text.Split(x[Numbers], ", "), Number.From),
b = Table.FromRows({List.Repeat({a}, x[N])}),
c = List.Accumulate(
{"1" .. Text.From(x[N])},
b,
(s, c) => Table.ExpandListColumn(s, "Column" & c)
),
d = List.Distinct(
List.Select(List.Transform(Table.ToRows(c), each {_, List.Sum(_)}), each _{1} = x[Sum])
),
e = List.Transform(d, each List.Sort(_{0})),
f = if List.Count(List.Distinct(e)) = 1 then e else List.Transform(e, List.Distinct),
g = List.Transform(
List.Distinct(List.Select(f, each List.Count(_) = x[N])),
each List.Sort(_, (t) => List.PositionOf(a, t))
),
h = Text.Combine(
List.Transform(
g,
each "(" & Text.Combine(List.Transform(_, (y) => Text.From(y)), ", ") & ")"
),
", "
)
in
h
)[[Answer]]
in
SolPower Query solution 4 for Unique Combinations Matching Sum, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Numbers", type text}, {"N", Int64.Type}, {"Sum", Int64.Type}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"NumberList",
each
let
L = List.Transform(Text.Split([Numbers], ", "), Number.FromText),
N = List.Count(L),
Subsets = List.Transform(
List.Transform(
{0 .. Number.Power(2, N) - 1},
(i) =>
List.Transform(
{0 .. N - 1},
(j) =>
if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1
then L{j}
else null
)
),
List.RemoveNulls
),
Filtered = List.Select(
List.Distinct(Subsets),
(s) => List.Count(s) = [N] and List.Sum(s) = [Sum]
),
ToText = Text.Combine(
List.Transform(
Filtered,
each "(" & Text.Combine(List.Transform(_, Number.ToText), ", ") & ")"
),
", "
)
in
ToText
)
in
#"Added Custom"
I'm reusing my subset code from here:
https://community.fabric.microsoft.com/t5/Community-Blog/Combinatorics-in-Power-Query-part1/bc-p/2221143/highlight/true#M3151
Solving the challenge of Unique Combinations Matching Sum with Excel
Excel solution 1 for Unique Combinations Matching Sum, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A8,
B2:B8,
C2:C8,
LAMBDA(n,
I,
s,
LET(t,
TEXTSPLIT(
n,
", "
),
c,
REDUCE(
"",
t,
LAMBDA(
a,
n,
VSTACK(
a,
a&", "&n
)
)
),
TEXTJOIN(", ",
,
UNIQUE(FILTER("("&MID(
c,
3,
99
)&")",
(LEN(
c
)-LEN(
SUBSTITUTE(
c,
" ",
)
)=I)*(REDUCE(
0,
t,
LAMBDA(
a,
n,
VSTACK(
a,
a+n
)
)
)=s),
""))))))Excel solution 2 for Unique Combinations Matching Sum, proposed by John V.:
=MAP(
A2:A8,
B2:B8,
C2:C8,
LAMBDA(
d,
e,
f,
LET(
n,
TEXTSPLIT(
d,
","
),
c,
COLUMNS(
n
),
b,
BASE(
SEQUENCE(
2^c-1
),
2,
c
),
s,
SEQUENCE(
,
c
),
r,
MAP(
b,
LAMBDA(
y,
LET(
v,
TOCOL(
s/MID(
y,
s,
1
),
2
),
z,
INDEX(
--n,
v
),
ROWS(
v
)&"-"&SUM(
z
)&"|("&ARRAYTOTEXT(
z
)&")"
)
)
),
ARRAYTOTEXT(
UNIQUE(
FILTER(
TEXTAFTER(
r,
"|"
),
TEXTBEFORE(
r,
"|"
)=e&"-"&f,
""
)
)
)
)
)
)Excel solution 3 for Unique Combinations Matching Sum, proposed by محمد حلمي:
=MAP(A2:A8,B2:B8,C2:C8,LAMBDA(a,b,c,LET(
i,IFERROR(TEXTSPLIT(TEXTAFTER(
REDUCE(0,TEXTSPLIT(a,", "),LAMBDA(a,d,
VSTACK(a,a&" "&d)))," ",SEQUENCE(,b))," "),99)+0,
IFERROR(TEXTJOIN(", ",,
BYROW(UNIQUE(FILTER(i,MMULT(i,SEQUENCE(b)^0)=c)),
LAMBDA(a,"("&TEXTJOIN(", ",,a)&")"))),""))))Excel solution 4 for Unique Combinations Matching Sum, proposed by Timothée BLIOT:
=MAP(
A2:A8,
B2:B8,
C2:C8,
LAMBDA(
i,
j,
k,
LET(
A,
TEXTSPLIT(
i,
","
),
B,
CHAR(
SEQUENCE(
,
COUNTA(
A
)
)+64
),
C,
A&B,
D,
REDUCE(
"",
SEQUENCE(
j
),
LAMBDA(
w,
v,
LET(
E,
FILTER(
w,
LEN(
w
)-LEN(
SUBSTITUTE(
w,
":",
""
)=v-1
)
),
TOCOL(
IF(
ISERR(
FIND(
C,
E
)
),
E&C&":",
1/0
),
3
)
)
)
),
E,
FILTER(
D,
MAP(
D,
LAMBDA(
x,
SUM(
--REGEXEXTRACT(
x,
"d+",
1
)
)
)
) =k,
""
),
ARRAYTOTEXT(
UNIQUE(
IF(
E="",
"",
"("&MAP(
E,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
A,
MAP(
C,
LAMBDA(
y,
ISNUMBER(
XMATCH(
y,
REGEXEXTRACT(
x,
"d+w+",
1
)
)
)
)
),
""
)
)
)
)&")"
)
)
)
)
)
)Excel solution 5 for Unique Combinations Matching Sum, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A8, B2:B8, C2:C8, LAMBDA(a, b, c, LET(_n,--TEXTSPLIT(a, ,", "),_c, COUNT(_n),_m,--MID(BASE(SEQUENCE(2^_c-1), 2,_c), SEQUENCE( ,_c), 1),_d,TOROW(_n)/_m, IFERROR(ARRAYTOTEXT(UNIQUE("("&BYROW(FILTER(_d,BYROW(_d, LAMBDA(r, COUNT(r)&SUM(TOCOL(r, 2))))=b&c), LAMBDA(w,ARRAYTOTEXT(TOCOL(w, 2))))&")")), ""))))Excel solution 6 for Unique Combinations Matching Sum, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A8,
B2:B8,
C2:C8,
LAMBDA(
x,
y,
z,
LET(
n,
--TEXTSPLIT(
x,
", "
),
IFERROR(
ARRAYTOTEXT(
DROP(
TOROW(
UNIQUE(
REDUCE(
"",
SEQUENCE(
500
),
LAMBDA(
a,
b,
VSTACK(
a,
LET(
m,
RANDARRAY(
,
COUNTA(
n
)
),
t,
SORT(
TAKE(
SORTBY(
n,
m
),
,
y
),
,
,
1
),
IF(
SUM(
t
)=z,
"("&ARRAYTOTEXT(
t
)&")",
""
)
)
)
)
)
),
3
),
,
1
)
),
""
)
)
)
)Excel solution 7 for Unique Combinations Matching Sum, proposed by Pieter de Bruijn:
=MAP(A2:A8,B2:B8,C2:C8,LAMBDA(a,b,c,
LET( objective, c,
range, --TEXTSPLIT(a,,","),
combinations, LET(i,LAMBDA(x,DROP(TEXTAFTER(REDUCE("",x,LAMBDA(combined_temp,comb,VSTACK(combined_temp,combined_temp&", "&comb))),", "),1)),i(FILTER(range,range<=objective))),
summed_combinations, DROP(REDUCE(0,combinations,LAMBDA(summed_temp,comb_row,LET(split,--TEXTSPLIT(comb_row,", "),VSTACK(summed_temp,SUM(split*(COUNT(split)=b)))))),1),
res,UNIQUE(FILTER(combinations,summed_combinations=objective,"")),
joinres,TEXTJOIN("), (",1,res),
IF(joinres="","","("&joinres&")"))))Excel solution 8 for Unique Combinations Matching Sum, proposed by Ziad A.:
=ARRAYFORMULA(
IFNA(
MAP(
A2:A8,
B2:B8,
C2:C8,
LAMBDA(
x,
y,
z,
LET(
s,
SPLIT(
x,
", "
),
c,
COUNT(
s
),
d,
DEC2BIN(
SEQUENCE(
2^c
)-1,
c
),
f,
FILTER(
d,
LEN(
& SUBSTITUTE(
d,
0,
)
)=y
),
b,
UNIQUE(
BYROW(
--REGEXEXTRACT(
f,
REPT(
"(.)",
LEN(
f
)
)
),
LAMBDA(
r,
FILTER(
s,
r
)
)
)
),
"("&TEXTJOIN(
"), (",
1,
BYROW(
FILTER(
b,
MMULT(
b,
SEQUENCE(
y
)^0
)=z
),
LAMBDA(
r,
TEXTJOIN(
", ",
1,
r
)
)
)
)&")"
)
)
)
)
)
Taking as example the numbers in A2. The formula generates the following sequence of all the possible combinations (where 0 indicates "excluded" and 1 indicates "included").
000000
000001
000010
...
111111
After that all the combinations that don't have exactly "N" 1s are filtered out.
000111
001011
001101
...
111000
Then we are filtering out the values in "Numbers" that correspond to the 0s positions and summing what remains.
If the sum is equal to "Sum" we keep the values,
otherwise we remove them. (We could also filter out the values that correspond to the 1s position since the sequence is symmetric)Excel solution 9 for Unique Combinations Matching Sum, proposed by Abdelrahman Omer, MBA, PMP:
Bo Rydobon 🇹🇭 Excel solution 10 for Unique Combinations Matching Sum, proposed by Andres Rojas Moncada:
=MAP(
A2:A8,
B2:B8,
C2:C8,
LAMBDA(
_cad,
_lon,
_tot,
LET(
_nums,
DIVIDIRTEXTO(
_cad,
", "
)*1,
_cant,
CONTARA(
_nums
),
_comb,
EXTRAE(
DEC.A.BIN(
SECUENCIA(
2^_cant,
1,
0
),
_cant
),
SECUENCIA(
1,
_cant
),
1
)*1,
_posi,
FILTRAR(
_comb,
BYROW(
_comb,
LAMBDA(
_fil,
Y(
SUMA(
_fil
)=_lon,
SUMA(
_fil*_nums
)=_tot
)
)
),
""
),
UNIRCADENAS(
", ",
1,
SI.ERROR(
UNICOS(
BYROW(
_posi,
LAMBDA(
_f,
"("&UNIRCADENAS(
", ",
1,
SI(
_f,
_nums,
""
)
)&")"
)
)
),
""
)
)
)
)
)Excel solution 11 for Unique Combinations Matching Sum, proposed by Kerwin Tan CPA:
= pd.read_excel(
file_path
)
def find_combo(
_list,
_n,
_sum
):
unique = set()
for combo in combinations(
_list,
_n
):
if sum(
combo
) == _sum:
unique.add(
tuple(
combo
)
)
return list(
unique
)
def process_row(
row
):
nums_list = [int(
i
) for i in row['Numbers'].split(
',
'
)]
return find_combo(
nums_list,
int(
row["N"]
),
int(
row["Sum"]
)
)
df['Answer'] = df.apply(
process_row,
axis=1
)Solving the challenge of Unique Combinations Matching Sum with Python
Python solution 1 for Unique Combinations Matching Sum, proposed by Vijay Tumbur:
Here is my python submission:
import pandas as pd
import itertools as it
def nDigitsForSum(num,n,summed):
list1 = [j for j in it.combinations([int(i) for i in num.split(', ')],n) if sum(j) == summed]
return list(set(list1))
a = data.iloc[:,0].values.tolist()
b = data.iloc[:,1].values.tolist()
c = data.iloc[:,2].values.tolist()
print(list(map(nDigitsForSum,a,b,c)))
Solving the challenge of Unique Combinations Matching Sum with Python in Excel
Python in Excel solution 1 for Unique Combinations Matching Sum, proposed by Bo Rydobon 🇹🇭:
from itertools import combinations
[', '.join(np.unique(['('+', '.join(c)+')' for c in combinations(n.split(', '),i) if sum(map(int,c)) ==s])) for n,i,s in xl("A2:C8").values]Python in Excel solution 2 for Unique Combinations Matching Sum, proposed by John V.:
Hi everyone!
import itertools
def z(v, n, s):
x = list(map(int, v.split(",")))
return ", ".join(list(map(str, set([c for c in itertools.combinations(x, n) if sum(c) == s]))))
xl("A1:C8", headers=True).apply(lambda x: z(x['Numbers'], x['N'], x['Sum']), axis=1).values
Blessings!
Python in Excel solution 3 for Unique Combinations Matching Sum, proposed by 🇰🇷 Taeyong Shin:
from itertools import combinations
df = xl("A1:C8", headers = True)
def find_combinations(row):
combs = set(combinations(row['Temp'], row['N']))
return ', '.join(str(c) for c in combs if sum(c) == row['Sum'])
df['Temp'] = df['Numbers'].str.split(', ').map(lambda x: list(map(int, x)))
df.apply(find_combinations, axis = 1)
Python in Excel solution 4 for Unique Combinations Matching Sum, proposed by Diarmuid Early:
def dimBin(num,ln):
sh = bin(num)[2:]
return (ln - len(sh)) * "0" + sh
lists = [[[int(n) for i, n in enumerate(a.split(", ")) if dimBin(k,len(a.split(", ")))[i]=="1"] for k in range(1,2**len(a.split(", ")))] for a in xl("A2:A8")[0]]
[", ".join(pd.unique(["(" + ", ".join(map(str,lst)) + ")" for lst in lstLst if len(lst)==N and sum(lst)==sm])) for lstLst, N, sm in zip(lists, xl("B2:B8")[0], xl("C2:C8")[0])]
I'm saving all my Python solutions to these challenges here if anyone wants to explore:
bit.ly/PythonLearningFolder
Solving the challenge of Unique Combinations Matching Sum with R
R solution 1 for Unique Combinations Matching Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("N Digits Existing for a Sum.xlsx")
generate_combinations_string <- function(numbers_string, n, target_sum) {
numbers <- str_split(numbers_string, ", ") %>%
unlist() %>%
as.numeric()
combinations <- combn(numbers, n, simplify = TRUE) %>%
t() %>%
as_tibble()
valid_combinations <- combinations %>%
mutate(sum = pmap_dbl(., ~sum(c(...)))) %>%
filter(sum == target_sum) %>%
select(-sum) %>%
unite("vector", everything(), sep = ", ") %>%
mutate(vector = paste0("(", vector, ")"))
all_vectors <- paste(sort(unique(valid_combinations$vector), decreasing = T), collapse = ", ")
return(all_vectors)
}
result = input %>%
mutate(my_answer = pmap_chr(list(Numbers, N, Sum), generate_combinations_string))
&&