Extract all item codes that are repeated at least in 3 out of 4 lists presented in the question table.
📌 Challenge Details and Links
Challenge Number: 11
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Identify Frequent Codes with Power Query
Power Query solution 1 for Identify Frequent Codes, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Re = [
a = List.Combine(List.Transform(Table.ToColumns(Source), List.Distinct)),
b = List.Select(List.Distinct(a), each List.Count(List.Select(a, (x) => x = _)) >= 3)
][b]
in
RePower Query solution 2 for Identify Frequent Codes, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DistinctList = List.Combine(List.Transform(Table.ToColumns(Source), each List.Distinct(_))),
ToTablle = Table.FromList(
DistinctList,
Splitter.SplitByNothing(),
{"Item Code"},
null,
ExtraValues.Error
),
Group = Table.Group(ToTablle, {"Item Code"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
FilterNClean = Table.Sort(
Table.RemoveColumns(Table.SelectRows(Group, each ([Count] = 3 or [Count] = 4)), "Count"),
{"Item Code", Order.Ascending}
)
in
FilterNCleanPower Query solution 3 for Identify Frequent Codes, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = List.Combine(List.Transform(Table.ToColumns(S), each List.Distinct(_))),
b = Table.Group(Table.FromColumns({a}),{"Column1"},{{"C", each _}})[C],
c = List.Select(b, each Table.RowCount(_)>=3),
Sol = Table.RenameColumns(Table.Sort(Table.Distinct(Table.Combine(c)),{{"Column1",0}}),{"Column1","Item Code"})
in
SolPower Query solution 4 for Identify Frequent Codes, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Distinc = List.Distinct(List.Combine(Table.ToColumns(Source))),
Lista = Table.ToColumns(
Table.FromRows(List.Zip(List.Transform(Table.ToColumns(Source), List.Distinct)))
),
Comb = List.RemoveNulls(
List.Combine(
List.Transform(
Distinc,
each List.Transform(Lista, (x) => if List.Contains(x, _) then _ else null)
)
)
),
Table = Table.FromColumns({Comb}, {"Item Code"}),
Sol = Table.Sort(
Table.SelectRows(
Table.Group(Table, {"Item Code"}, {{"A", each Table.RowCount(_)}}),
each [A] >= 3
),
{{"A", Order.Descending}, "Item Code"}
)[[Item Code]]
in
SolPower Query solution 5 for Identify Frequent Codes, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(
#"Unpivoted Columns",
{"Value", "Attribute"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"Merged"
),
#"Removed Duplicates" = Table.Distinct(#"Merged Columns"),
#"Split Column by Position" = Table.SplitColumn(
#"Removed Duplicates",
"Merged",
Splitter.SplitTextByPositions({0, 5}, false),
{"Item Code", "Merged.2"}
),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position", {"Merged.2"}),
#"Grouped Rows" = Table.Group(
#"Removed Columns",
{"Item Code"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 2),
#"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Item Code", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows", {"Count"})
in
#"Removed Columns1"Power Query solution 6 for Identify Frequent Codes, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
D = Table.DemoteHeaders(S),
Z = Table.RenameColumns(
D,
{{"Column1", "C1"}, {"Column2", "C2"}, {"Column3", "C3"}, {"Column4", "C4"}}
),
C = Table.TransformColumnTypes(
Z,
{{"C1", type text}, {"C2", type text}, {"C3", type text}, {"C4", type text}}
),
T = Table.Transpose(C),
U = Table.UnpivotOtherColumns(T, {"Column1"}, "Item Code", "c"),
R = Table.RemoveColumns(U, {"Item Code"}),
G = Table.Group(R, {"c"}, {{"Tbl", each _, type table [Column1 = text, c = text]}}),
A = Table.AddColumn(G, "Co", each List.Count(List.Distinct([Tbl][Column1]))),
F = Table.SelectRows(A, each [Co] >= 3),
S2 = Table.Sort(F, {{"c", Order.Ascending}}),
R2 = Table.SelectColumns(S2, {"c"}),
Sol = Table.RenameColumns(R2, {{"c", "Item Code"}})
in
SolPower Query solution 7 for Identify Frequent Codes, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Table.Group(
Table.FromList(
List.Combine(List.Transform(Table.ToColumns(Source), (x) => List.Distinct(x))),
Splitter.SplitByNothing()
),
"Column1",
{{"Freq", each Table.RowCount(_)}}
),
#"Filtered Rows" = Table.SelectRows(Custom1, each [Freq] > 2)[[Column1]],
#"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Column1", Order.Ascending}})
in
#"Sorted Rows"Solving the challenge of Identify Frequent Codes with Excel
Excel solution 1 for Identify Frequent Codes, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
B3:E16,
TOCOL(
MAP(
SORT(
UNIQUE(
TOCOL(
z
)
)
),
LAMBDA(
u,
IFS(
COUNT(
BYCOL(
z,
LAMBDA(
x,
XMATCH(
u,
x
)
)
)
)>2,
u
)
)
),
3
)
)Excel solution 2 for Identify Frequent Codes, proposed by 🇰🇷 Taeyong Shin:
=LET(
u,
SORT(
UNIQUE(
TOCOL(
B3:E16
)
)
),
FILTER(
u,
REDUCE(
0,
B2:E2,
LAMBDA(
a,
v,
a+SIGN(
COUNTIF(
XLOOKUP(
v,
B2:E2,
B3:E16
),
u
)
)
)
)>2
)
)Excel solution 3 for Identify Frequent Codes, proposed by محمد حلمي:
=LET(
b,
B3:E16,
i,
SORT(
UNIQUE(
TOCOL(
b
)
)
),
FILTER(
i,
MAP(
i,
LAMBDA(
x,
SUM(
N(
BYCOL(
b,
LAMBDA(
a,
SUM(
N(
a=x
)
)
)
)>0
)
)
)
)>2
)
)Excel solution 4 for Identify Frequent Codes, proposed by Oscar Mendez Roca Farell:
=TOCOL(
MAP(
SORT(
UNIQUE(
TOCOL(
B4:E17
)
)
),
LAMBDA(
a,
IF(
SUM(
N(
BYCOL(
B4:E17,
LAMBDA(
c,
SUM(
N(
c=a
)
)
)
)>0
)
)>2,
a,
1/0
)
)
),
2
)Excel solution 5 for Identify Frequent Codes, proposed by Julian Poeltl:
=LET(
QT,
B3:E16,
UNIQ,
SORT(
UNIQUE(
TOCOL(
QT
)
)
),
COUNT,
MAP(
UNIQ,
LAMBDA(
U,
COUNT(
BYCOL(
QT,
LAMBDA(
QT,
XMATCH(
U,
QT
)
)
)
)
)
),
FILTER(
UNIQ,
COUNT>2
)
)Excel solution 6 for Identify Frequent Codes, proposed by Kris Jaganah:
=LET(a,
UNIQUE(
TOCOL(
B3:E16&B2:E2
)
),
b,
LEFT(
a,
5
),
SORT(UNIQUE(FILTER(b,
MAP(b,
LAMBDA(x,
SUM(--(b=x))))>2))))Excel solution 7 for Identify Frequent Codes, proposed by John Jairo Vergara Domínguez:
=LET(
u,
UNIQUE(
TOCOL(
B3:E16
)
),
SORT(
FILTER(
u,
MAP(
u,
LAMBDA(
x,
SUM(
--BYCOL(
B3:E16=x,
OR
)
)
)
)>2
)
)
)Excel solution 8 for Identify Frequent Codes, proposed by Sunny Baggu:
=LET( _a,
SORT(
UNIQUE(
TOCOL(
B3:E16
)
)
), _b,
MAP(
_a,
LAMBDA(
x,
SUM(
N(
BYCOL(
N(
B3:E16 = x
),
LAMBDA(
a,
SUM(
a
)
)
) > 0
)
)
)
), FILTER(
_a,
_b > 2
))Excel solution 9 for Identify Frequent Codes, proposed by An Nguyen:
=LET(tbl,
B3:E16,
newtbl,
REDUCE(
NA,
SEQUENCE(
4
),
LAMBDA(
state,
current,
HSTACK(
state,
UNIQUE(
INDEX(
tbl,
,
current
)
)
)
)
), item,
TOCOL(
newtbl,
3
),
SORT(FILTER(UNIQUE(
item
), MMULT( --(UNIQUE(
item
) = TOROW(
item
)),
SEQUENCE(
COUNTA(
item
)
)^0)>2)))Excel solution 10 for Identify Frequent Codes, proposed by Andy Heybruch:
=LET(
codes,
SORT(
UNIQUE(
TOCOL(
B3:E16
)
)
),
FILTER(
codes,
BYROW(
codes,
LAMBDA(
b,
SUM(
BYCOL(
B3:E16,
LAMBDA(
a,
--ISNUMBER(
XMATCH(
b,
a,
0
)
)
)
)
)
)
)>2
)
)Excel solution 11 for Identify Frequent Codes, proposed by Asheesh Pahwa:
=LET(L,
B3:E16,
b,
UNIQUE(
TOCOL(
L
)
),
d,
DROP(REDUCE("",
SEQUENCE(
ROWS(
b
)
),
LAMBDA (x,
y VSTACK(
x,
LET(
q,
BYCOL(
L,
LAMBDA(
z,
LET(
a,
INDEX(
b,
y,
)=z,
CONCAT(
FILTER(
z,
a
)
)
)
)
),
SUM(
--ISTEXT(
q
)
)
)
))),
1),
FILTER(
b,
d>2
))Excel solution 12 for Identify Frequent Codes, proposed by Hussein SATOUR:
=LET(
a,
TEXTBEFORE(
UNIQUE(
TOCOL(
B3:E16&"/"&B2:E2
)
),
"/"
),
b,
UNIQUE(
a
),
FILTER(
b,
MAP(
b,
LAMBDA(
x,
COUNTA(
FILTER(
a,
a=x
)
)>2
)
)
)
)Excel solution 13 for Identify Frequent Codes, proposed by Pieter de B.:
=TOCOL(
MAP(
UNIQUE(
TOCOL(
B3:E16
)
),
LAMBDA(
x,
IFS(
SUM(
SIGN(
BYCOL(
N(
x=B$3:E$16
),
SUM
)
)
)>2,
x
)
)
),
2
)Excel solution 14 for Identify Frequent Codes, proposed by Surendra Reddy:
=LET(
data,
B3:E16,unique_list,
SORT(
UNIQUE(
TOCOL(
data
)
)
),criteria,
MAP(unique_list,
LAMBDA(x,
SUM((BYCOL(
XMATCH(
data,
x
),
COUNT
)>0)*1)>=3)),FILTER(
unique_list,
criteria
))Excel solution 15 for Identify Frequent Codes, proposed by Tyler Cameron:
=LET(t,
FILTER(UNIQUE(
B3:B16
),
MAP(UNIQUE(
B3:B16
),
LAMBDA(x,
LET(a,
B3:E16,
SUM(BYCOL(a,
LAMBDA(u,
--(COUNTIF(
u,
x
)>0))))>2)))),
SORTBY(
t,
INT(
TEXTAFTER(
t,
"-0"
)
),
1
))Solving the challenge of Identify Frequent Codes with R
R solution 1 for Identify Frequent Codes, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/CH-011.xlsx", range = "B2:E16")
test = read_excel("files/CH-011.xlsx", range = "K2:K6")
result = input %>%
pivot_longer(cols = everything(), names_to = "columns", values_to = "codes") %>%
group_by(codes) %>%
summarise(is_in_col = n_distinct(columns)) %>%
filter(is_in_col >= 3) %>%
select(-is_in_col)