Find the top 3 max product from the combination of numbers from 3 columns (Number1, Number2, Number3). List product and corresponding numbers from 3 columns.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 578
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Top Three Column Products with Power Query
Power Query solution 1 for Top Three Column Products, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.ToColumns( A),
C = Table.ColumnNames(A),
D = hashtag#table( C, {Table.ToColumns( A)}),
E = List.Accumulate(C , D,(x,y)=> Table.ExpandListColumn(x, y)),
F = Table.AddColumn(E, "Product", each List.Product(Record.ToList( _) )),
G = Table.MaxN( F ,{"Product"},3),
H = Table.SelectColumns(G, List.Combine({{List.Last( Table.ColumnNames( G))} ,C }))
in
H
Power Query solution 2 for Top Three Column Products, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumnTypes(
A,
{{"Number1", type text}, {"Number2", type text}, {"Number3", type text}}
),
C = Table.ToColumns(B),
D = List.Zip(C),
E = List.TransformMany(D, each C{1}, (x, y) => x{0} & "*" & y),
F = List.TransformMany(E, each C{2}, (v, w) => v & "*" & w),
G = Table.FromRows(List.Transform(F, each {Expression.Evaluate(_), _}), {"Product", "Number"}),
H = Table.SplitColumn(G, "Number", each Text.Split(_, "*")),
I = Table.TransformColumnTypes(
H,
{{"Number.1", Int64.Type}, {"Number.2", Int64.Type}, {"Number.3", Int64.Type}}
),
J = Table.MaxN(I, {"Product"}, 3)
in
JPower Query solution 3 for Top Three Column Products, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl = List.Accumulate(
List.Skip(Table.ColumnNames(Source)),
Source[[Number1]],
(s, c) =>
Table.ExpandTableColumn(Table.AddColumn(s, c, each Table.SelectColumns(Source, c)), c, {c})
),
Sol = Table.SelectColumns(
Table.MaxN(
Table.AddColumn(Tbl, "Product", each List.Product(Record.ToList(_))),
each [Product],
3
),
{"Product"} & Table.ColumnNames(Source)
)
in
SolPower Query solution 4 for Top Three Column Products, proposed by Brian Julius:
let
Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]),
Proc = List.Transform(
Source,
each [
a = _,
b = List.FirstN(List.Sort(a, Order.Descending), 3),
c = Table.FromColumns({b, {1, 1, 1}}, {"a", "b"})
][c]
),
Joins = Table.Distinct(
Table.RemoveColumns(
Table.Join(
Table.Join(
Table.RenameColumns(Proc{0}, {"a", "Number1"}),
"b",
Table.RenameColumns(Proc{1}, {"a", "Number2"}),
"b"
),
"b",
Table.RenameColumns(Proc{2}, {"a", "Number3"}),
"b"
),
"b"
)
),
AddProd = Table.Sort(
Table.AddColumn(Joins, "Product", each [Number1] * [Number2] * [Number3]),
{"Product", Order.Descending}
),
Filter = Table.ReorderColumns(
Table.SelectRows(AddProd, each List.ContainsAny({[Product]}, List.FirstN(AddProd[Product], 3))),
{"Product", "Number1", "Number2", "Number3"}
)
in
FilterPower Query solution 5 for Top Three Column Products, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.MaxN(
Table.FromRecords(
List.TransformMany(
List.TransformMany(Source[Number1], each Source[Number2], (x, y) => {x * y, x, y}),
each Source[Number3],
(x, y) => [Product = x{0} * y, Number1 = x{1}, Number2 = x{2}, Number3 = y]
)
),
"Product",
3
)
in
ResultPower Query solution 6 for Top Three Column Products, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToColumns(S),
b = List.TransformMany(a{0}, (x) => a{1}, (x, y) => Text.From(x) & "-" & Text.From(y)),
c = List.TransformMany(b, (x) => a{2}, (x, y) => Text.From(x) & "-" & Text.From(y)),
d = List.Transform(c, each Text.Split(_, "-")),
e = List.Transform(d, each List.Transform(_, Number.From)),
f = Table.FromRows(List.Transform(e, each {_{0} * _{1} * _{2}} & {_{0}} & {_{1}} & {_{2}})),
g = List.Zip({Table.ColumnNames(f), {"Product"} & Table.ColumnNames(S)}),
Sol = Table.MaxN(Table.RenameColumns(f, g), "Product", 3)
in
SolPower Query solution 7 for Top Three Column Products, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Func = (x as list) => List.Transform(List.MaxN(List.Distinct(x), 3), each {_}),
Cols = List.Transform(Table.ToColumns(Source), each Func(_)),
Rows = [
A = List.Accumulate(
{1 .. List.Count(Cols) - 1},
Cols{0},
(a, v) => List.TransformMany(a, (x) => Cols{v}, (x, y) => x & y)
),
B = List.Transform(A, each {List.Product(_)} & _)
][B],
Res = Table.MaxN(Table.FromRows(Rows, {"Product"} & Table.ColumnNames(Source)), "Product", 3)
in
ResPower Query solution 8 for Top Three Column Products, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToColumns(A),
C = List.TransformMany(
B{0},
each List.TransformMany(B{1}, each B{2}, (x, y) => {x, y}),
(x, y) => {x * y{0} * y{1}, x, y{0}, y{1}}
),
D = Table.FromRows(
List.FirstN(List.Sort(C, {each _{0}, 1}), 3),
{"Product"} & Table.ColumnNames(A)
)
in
DPower Query solution 9 for Top Three Column Products, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Expand = List.Accumulate(Table.ColumnNames(Source),
hashtag#table({"Number1","Number2","Number3"},{List.Transform( Table.ToColumns(Source),each List.FirstN(List.Sort(_,-1),3))}),
(a,c)=>Table.ExpandListColumn(a,c)),
Multiply = Table.AddColumn(Expand, "Product", each List.Product({[Number1],[Number2],[Number3]})),
Sort = Table.Sort(Multiply,{"Product",1}),
Top3 = Table.FirstN(Sort,3),
Reorder = Table.ReorderColumns(Top3,{"Product", "Number1", "Number2", "Number3"})
in
Reorder
Power Query solution 10 for Top Three Column Products, proposed by Dominic Walsh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Number1 = Table.RemoveColumns(Source, {"Number2", "Number3"}),
Num2 = Table.AddColumn(Number1, "Num2", each Source),
Number2 = Table.ExpandTableColumn(Num2, "Num2", {"Number2"}, {"Number2"}),
Num3 = Table.AddColumn(Number2, "Num3", each Source),
Number3 = Table.ExpandTableColumn(Num3, "Num3", {"Number3"}, {"Number3"}),
Product = Table.AddColumn(
Number3,
"Product",
each List.Product({[Number1], [Number2], [Number3]}),
type number
),
Order = Table.ReorderColumns(Product, {"Product", "Number1", "Number2", "Number3"}),
Sort = Table.Sort(Order, {{"Product", Order.Descending}}),
Take = Table.FirstN(Sort, 3)
in
TakePower Query solution 11 for Top Three Column Products, proposed by Fowmy Abdulmuttalib:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = List.TransformMany(
t1[Number1],
each t1[Number2],
(x, y) =>
List.TransformMany(
t1[Number3],
each {1},
(x1, y1) => [Product = x * y * x1, Number1 = x, Number2 = y, Number3 = x1]
)
),
t3 = Table.FromRecords(List.Combine(t2)),
t4 = Table.MaxN(t3, "Product", 3)
in
t4Solving the challenge of Top Three Column Products with Excel
Excel solution 1 for Top Three Column Products, proposed by Bo Rydobon 🇹🇭:
=LET(a,--TEXTSPLIT(CONCAT(REDUCE("",A3:C3,LAMBDA(a,v,TOCOL(a&TOROW(TAKE(SORT(-v:C11),3,1)))))&"|"),"-","|",1),TAKE(SORT(HSTACK(BYROW(a,PRODUCT),a),,-1),3))Excel solution 2 for Top Three Column Products, proposed by Bo Rydobon 🇹🇭:
=TOCOL(TOCOL(A3:A11&-TOROW(B3:B11))&-TOROW(C3:C11))
Make it more dynamic
=LET(a,REDUCE("",A3:C3,LAMBDA(a,v,TOCOL(a&TOROW(-TAKE(v:C11,,1)),))),a)
Get the large 3 of each column to reduce 9^3=729 to 3^3=27 rows
=REDUCE("",A3:C3,LAMBDA(a,v,TOCOL(a&TOROW(TAKE(SORT(-v:C11),3,1)),))Excel solution 3 for Top Three Column Products, proposed by John V.:
=LET(
a,
-A3:A11,
b,
B3:B11,
c,
C3:C11,
f,
TOROW,
TAKE(
-SORT(
HSTACK(
TOCOL(
f(
a*f(
b
)
)*c
),
MID(
TOCOL(
f(
a&f(
-b
)
)&-c
),
{1,
4,
7},
3
)
)
),
3
)
)Excel solution 4 for Top Three Column Products, proposed by Kris Jaganah:
=LET(a,
--TEXTSPLIT(ARRAYTOTEXT(TOCOL(TOROW(
A3:A11&"-"&TOROW(
B3:B11
)
)&"-"&(C3:C11))),
"-",
", "),
TAKE(
GROUPBY(
BYROW(
a,
PRODUCT
),
a,
SINGLE,
,
0,
-1
),
3
))Excel solution 5 for Top Three Column Products, proposed by Julian Poeltl:
=LET(
N,
TOCOL(
TOCOL(
A3:A11&","&TOROW(
B3:B11
)
)&","&TOROW(
C3:C11
)
),
P,
TOCOL(
TOCOL(
A3:A11*TOROW(
B3:B11
)
)*TOROW(
C3:C11
)
),
HSTACK(
LARGE(
P,
SEQUENCE(
3
)
),
--TEXTSPLIT(
TEXTJOIN(
"|",
,
TAKE(
SORTBY(
N,
P,
-1
),
3
)
),
",",
"|"
)
)
)Excel solution 6 for Top Three Column Products, proposed by Alejandro Campos:
=LET(
nA,
A3:A11,
nB,
B3:B11,
nC,
C3:C11,
combs,
SEQUENCE(
ROWS(
nA
) * ROWS(
nB
) * ROWS(
nC
)
),
iA,
INDEX(
nA,
MOD(
SEQUENCE(
ROWS(
combs
),
,
0
),
ROWS(
nA
)
) + 1
),
iB,
INDEX(
nB,
MOD(
INT(
SEQUENCE(
ROWS(
combs
),
,
0
) / ROWS(
nA
)
),
ROWS(
nB
)
) + 1
),
iC,
INDEX(nC,
INT(SEQUENCE(
ROWS(
combs
),
,
0
) / (ROWS(
nA
) * ROWS(
nB
))) + 1),
prod,
BYROW(
HSTACK(
iA,
iB,
iC
),
PRODUCT
),
sortedProducts,
SORT(
HSTACK(
prod,
iA,
iB,
iC
),
1,
-1
),
TAKE(
sortedProducts,
3
))Excel solution 7 for Top Three Column Products, proposed by Timothée BLIOT:
=LET(A,TOCOL(TOCOL(TOCOL(A3:A11)&":"&TOROW(B3:B11))&":"&TOROW(C3:C11)),B,MAP(A,LAMBDA(x,PRODUCT(--TEXTSPLIT(x,":")))),C,UNIQUE(FILTER (B,LARGE(UNIQUE(B),3)<=B)),SORT(HSTACK(C,TEXTSPLIT(TEXTJOIN( "|",,UNIQUE(FILTER(A,B>=MIN(C)))),":","|")),,-1))Excel solution 8 for Top Three Column Products, proposed by Duy Tùng:
=LET(
W,
TOROW,
a,
-TEXTSPLIT(
CONCAT(
TOCOL(
A3:A11&-W(
B3:B11
)
)&-W(
C3:C11
)&"/"
),
"-",
"/"
),
-TAKE(
SORT(
HSTACK(
BYROW(
a,
PRODUCT
),
a
)
),
3
)
)Excel solution 9 for Top Three Column Products, proposed by Sunny Baggu:
=LET(
a, A3:A11,
b, TOROW(B3:B11),
c, C3:C11,
_a, TOCOL(TOROW(a * b) * c),
_b, TOCOL(TOROW(a & "*" & b) & "*" & c),
_c, "*" & TAKE(SORTBY(_b, _a, -1), 3),
HSTACK(
TAKE(SORT(_a, , -1), 3),
TEXTBEFORE(TEXTAFTER(_c, "*", {1, 2, 3}), "*", , , 1)
)
)Excel solution 10 for Top Three Column Products, proposed by LEONARD OCHEA 🇷🇴:
=LET(
t,
A3:C11,
s,
SEQUENCE(
,
3
),
f,
MID(
BASE(
SEQUENCE(
9^3,
,
0
),
9,
3
),
s,
1
)+1,
i,
INDEX(
t,
f,
IF(
f,
s
)
),
TAKE(
SORT(
HSTACK(
BYROW(
i,
PRODUCT
),
i
),
1,
-1
),
3
)
)Excel solution 11 for Top Three Column Products, proposed by Pieter de B.:
=LET(L,LAMBDA(n,LARGE(n,{1;2;3})),X,TOCOL(TOCOL(L(A3:A11)*TOROW(L(B3:B11)))*TOROW(C3:C11)),Y,TOCOL(TOCOL(L(A3:A11)&"|"&TOROW(L(B3:B11)))&"|"&TOROW(C3:C11)),TAKE(SORT(HSTACK(X,--TEXTSPLIT(TEXTAFTER("|"&Y,"|",{1,2,3}),"|"&)),,-1),3))Excel solution 12 for Top Three Column Products, proposed by Jaroslaw Kujawa:
=LET(
d ; CONCAT(TOCOL(TOCOL(A3:A11&";"&TOROW(B3:B11))&";"&TOROW(C3:C11))&"|");
e ; DROP(TEXTSPLIT(d ; ";" ; "|") ; -1);
TAKE(SORT(HSTACK(BYROW(1*e ; PRODUCT) ; e) ; 1 ; -1) ; 3))Excel solution 13 for Top Three Column Products, proposed by Ankur Sharma:
=LET(
a,
A3:A11,
b,
TOROW(
B3:B11
),
c,
TOROW(
C3:C11
),
d,
TOCOL(
TOCOL(
a * b
) * c
),
e,
SORTBY(
TOCOL(
TOCOL(
a & ":" & b
) & ":" & c
),
d,
-1
),
HSTACK(
TAKE(
SORT(
d,
,
-1
),
3
),
TEXTSPLIT(
TEXTJOIN(
"-",
,
TAKE(
e,
3
)
),
":",
"-"
)
)
)Excel solution 14 for Top Three Column Products, proposed by JvdV –:
=LET(
c,
TOCOL,
r,
TOROW,
s,
-TEXTSPLIT(
CONCAT(
c(
c(
A.:.A&-r(
B3:B11
)
)&-r(
C3:C11
)&"|"
)
),
"-",
"|"
),
-TAKE(
SORT(
HSTACK(
BYROW(
s,
PRODUCT
),
s
)
),
3
)
)Excel solution 15 for Top Three Column Products, proposed by Cary Ballard, DML:
=LET(
data,
A3:C11,
u,
"-",
n,
IFS(
data <> "",
data & u
),
c,
REDUCE(
"",
SEQUENCE(
COLUMNS(
n
)
),
LAMBDA(
a,
v,
TOCOL(
a & TOROW(
INDEX(
n,
,
v
)
),
2
)
)
),
s,
TEXTSPLIT(
TEXTAFTER(
u & c & u,
u,
SEQUENCE(
,
COLUMNS(
data
)
)
),
u
),
p,
BYROW(
--s,
PRODUCT
),
l,
LARGE(
p,
SEQUENCE(
3
)
),
HSTACK(
l,
DROP(
REDUCE(
"",
SEQUENCE(
3
),
LAMBDA(
a,
v,
VSTACK(
a,
TAKE(
FILTER(
s,
INDEX(
l,
v
) = p
),
1
)
)
)
),
1
)
)
)Solving the challenge of Top Three Column Products with Python
Python solution 1 for Top Three Column Products, proposed by Konrad Gryczan, PhD:
import pandas as pd
from itertools import product
path = "578 Find Maximum Product.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="E:H", skiprows=1, nrows=3).rename(columns=lambda x: x.split('.')[0])
df = pd.DataFrame([(a, b, c, a * b * c) for a, b, c in product(input['Number1'], input['Number2'], input['Number3'])],
columns=['Number1', 'Number2', 'Number3', 'Product'])
result = df.nlargest(3, 'Product')[['Product', 'Number1', 'Number2', 'Number3']].reset_index(drop=True)
print(result.equals(test)) # True
Solving the challenge of Top Three Column Products with Python in Excel
Python in Excel solution 1 for Top Three Column Products, proposed by Alejandro Campos:
import itertools
df = xl("A2:C11", headers=True)
combinations = list(itertools.product(df['Number1'], df['Number2'], df['Number3']))
products = [(a * b * c, a, b, c) for a, b, c in combinations]
top_3_products = sorted(products, reverse=True)[:3]
top_3_df = pd.DataFrame(top_3_products, columns=['Product', 'Number1', 'Number2', 'Number3'])
top_3_df
Python in Excel solution 2 for Top Three Column Products, proposed by Anshu Bantra:
import itertools as itt
df = xl("A2:C11", headers=True)
pd.DataFrame(
sorted([(x*y*z, x,y,z) for x, y, z, in itt.product(df['Number1'], df['Number2'], df['Number3'])], reverse=True)[:3],
columns=['Product', 'Number1', 'Number2', 'Number3']
)
Solving the challenge of Top Three Column Products with R
R solution 1 for Top Three Column Products, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/578 Find Maximum Product.xlsx"
input = read_excel(path, range = "A2:C11")
test = read_excel(path, range = "E2:H5")
result = expand.grid(Number1 = input$Number1, Number2 = input$Number2, Number3 = input$Number3) %>%
mutate(Product = Number1 * Number2 * Number3) %>%
arrange(desc(Product)) %>%
slice(1:3) %>%
select(Product, everything())
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&&