Populate Fruits1, Fruits2 and Fruits3 against fruits beneath the counts. Ex. In Fruits3 column, Apple appears 2 times. Hence, Fruits3 will appear against Apple beneath count 2.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 566
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Assign Fruits to Tiers with Power Query
Power Query solution 1 for Assign Fruits to Tiers, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
U = Table.UnpivotOtherColumns(S, {}, "e", "Count"),
G = Table.Sort(Table.Group(U, {"e", "Count"}, {"c", each Text.From(Table.RowCount(_))}), {"c", 0}),
R = Table.Pivot(G, List.Distinct(G[c]), "c", "e", each Text.Combine(List.Sort(_), ", "))
in
R
Blessings!
Power Query solution 2 for Assign Fruits to Tiers, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {}, "A", "Count"),
C = Table.AddColumn(
B,
"Co",
each Text.From(List.Count(Table.SelectRows(B, (x) => x[A] = [A] and x[Count] = [Count])[Count]))
),
D = Table.Pivot(
C,
List.Sort(List.Distinct(C[Co])),
"Co",
"A",
each Text.Combine(List.Distinct(List.Sort(_)), ", ")
)
in
DPower Query solution 3 for Assign Fruits to Tiers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {}, "Head", "Count"),
Group = Table.Group(Unpivot, {"Head", "Count"}, {"C", each Text.From(Table.RowCount(_))}),
Pivot = Table.Pivot(
Group,
List.Sort(List.Distinct(Group[C]), each Number.From(_)),
"C",
"Head",
each Text.Combine(List.Sort(_), ", ")
)
in
PivotPower Query solution 4 for Assign Fruits to Tiers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {}, "A", "Count"),
Group = Table.Group(Unpivot, {"A", "Count"}, {{"B", each Text.From(Table.RowCount(_))}}),
Sol = Table.Pivot(
Group,
List.Sort(List.Distinct(Group[B])),
"B",
"A",
each Text.Combine(List.Sort(_), ", ")
)
in
SolPower Query solution 5 for Assign Fruits to Tiers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = List.Combine(Table.ToColumns(Source)),
B = List.Combine(
List.Transform(Table.ColumnNames(Source), each List.Repeat({_}, Table.RowCount(Source)))
),
Tbl = Table.SelectRows(Table.FromColumns({B, A}, {"C", "Count"}), each [Count] <> null),
Group = Table.Group(Tbl, {"C", "Count"}, {{"D", each Text.From(Table.RowCount(_))}}),
Sol = Table.Pivot(
Group,
List.Sort(List.Distinct(Group[D])),
"D",
"C",
each Text.Combine(List.Sort(_), ", ")
)
in
SolPower Query solution 6 for Assign Fruits to Tiers, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {}, "Column1", "Column2"),
Group = Table.Group(Unpivot, {"Column2", "Column1"}, {"Count", each List.Count([Column1])}),
Transform = Table.TransformColumnTypes(Group, {"Count", type text}),
Distinct = List.Distinct(Transform[Count]),
Pivot = Table.Pivot(
Transform,
Distinct,
"Count",
"Column1",
each Text.Combine(List.Sort(_), ", ")
),
SortedColums = {"Column2"} & List.Sort(List.Skip(Table.ColumnNames(Pivot))),
Result = Table.RenameColumns(Table.SelectColumns(Pivot, SortedColums), {"Column2", "Count"})
in
ResultPower Query solution 7 for Assign Fruits to Tiers, proposed by Ramiro Ayala Chávez:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S = Table.SelectRows,
G = Table.Group,
C = Table.RowCount,
R = Table.RenameColumns,
A = Table.AddColumn,
N = Table.ColumnNames,
T = Table.TransformColumnTypes,
a = S(G(Source, {"Fruits1"}, {"C", C}), each [Fruits1] <> null),
b = R(A(a, "F", each N(Source){0}), {"Fruits1", "Count"}),
c = S(G(Source, {"Fruits2"}, {"C", C}), each [Fruits2] <> null),
d = R(A(c, "F", each N(Source){1}), {"Fruits2", "Count"}),
e = S(G(Source, {"Fruits3"}, {"C", C}), each [Fruits3] <> null),
f = R(A(e, "F", each N(Source){2}), {"Fruits3", "Count"}),
g = Table.Sort(b & d & f, {"C", 0}),
Sol = Table.Pivot(
T(g, {"C", type text}),
List.Distinct(T(g, {"C", type text})[C]),
"C",
"F",
each Text.Combine(List.Sort(_), ", ")
)
in
SolPower Query solution 8 for Assign Fruits to Tiers, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.DemoteHeaders(S),
B = Table.Transpose(A),
C = Table.UnpivotOtherColumns(B, {"Column1"}, "Attribute", "Value"),
D = Table.Group(C, {"Column1", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
E = Table.Sort(D, {{"Count", Order.Ascending}}),
F = Table.Group(E, {"Value", "Count"}, {{"T", each _}}),
G = Table.AddColumn(F, "C", each Text.Combine([T][Column1], ",")),
H = Table.SelectColumns(G, {"Value", "Count", "C"}),
I = Table.Pivot(
Table.TransformColumnTypes(H, {{"Count", type text}}, "en-US"),
List.Sort(List.Distinct(Table.TransformColumnTypes(H, {{"Count", type text}}, "en-US")[Count])),
"Count",
"C"
)
in
IPower Query solution 9 for Assign Fruits to Tiers, proposed by Rafael González B.:
let
Source = Table, TTC = Table.TransformColumnTypes, G = "Group", C = "Count",
IndexCol = Table.AddIndexColumn(Source, "Index", 0, 1),
UnPvt = Table.UnpivotOtherColumns(IndexCol, {"Index"}, "Col", C),
REmt = Table.SelectRows(UnPvt, each ([Count] <> "")),
GrCount = Table.Group(REmt, {C, "Col"}, {{G, each Table.RowCount(_)}}),
CountTable = Table.Group(GrCount, {G}, {{"Sub",
each Table.Group(_, {C},
{{"LT", (t) => Text.Combine(List.Sort(t[Col]), ", ")}}
)
}}),
ExpCol = Table.ExpandTableColumn(CountTable, "Sub", {C, "LT"}, {C, "LT"}),
Sort = Table.Sort(ExpCol,{{G, 0}}),
Pivot = Table.Pivot(TTC(Sort, {{G, type text}}),
List.Distinct(TTC(Sort, {{G, type text}})[Group]), G, "LT")
in
Pivot
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 10 for Assign Fruits to Tiers, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
ColNameList = Table.ColumnNames(Source),
ColToList = Table.ToColumns(Source),
RemovedNulls = List.Transform(ColToList, each List.RemoveNulls(_)),
DistinctLIst = List.Transform(RemovedNulls, each List.Distinct(_)),
FruitCount = List.Transform(
{0 .. (List.Count(DistinctLIst) - 1)},
each List.Transform(
DistinctLIst{_},
(k) => {k, List.Count(List.Select(RemovedNulls{_}, (x) => x = k))}
)
),
ListWithFruitBucket = List.Transform(
{0 .. (List.Count(FruitCount) - 1)},
each List.Transform(FruitCount{_}, (k) => k & {ColNameList{_}})
),
CombinedLists = List.Combine(ListWithFruitBucket),
TableFromRows = Table.FromRows(CombinedLists),
SortedRows = Table.Sort(TableFromRows, {{"Column2", Order.Ascending}}),
PivotedColumn = Table.Pivot(
Table.TransformColumnTypes(SortedRows, {{"Column2", type text}}, "en-US"),
List.Distinct(
Table.TransformColumnTypes(SortedRows, {{"Column2", type text}}, "en-US")[Column2]
),
"Column2",
"Column3",
each Text.Combine(_, ",")
),
RenamedCol = Table.RenameColumns(PivotedColumn, {{"Column1", "Count"}})
in
RenamedColSolving the challenge of Assign Fruits to Tiers with Excel
Excel solution 1 for Assign Fruits to Tiers, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A3:C14,
u,
SORT(
UNIQUE(
TOCOL(
z,
3
)
)
),
r,
DROP(
REDUCE(
0,
u,
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
LET(
b,
BYCOL(
N(
z=v
),
SUM
),
MAP(
SEQUENCE(
,
MAX(
b
)
),
LAMBDA(
n,
TEXTJOIN(
", ",
,
REPT(
A2:C2,
b=n
)
)
)
)
)
),
""
)
)
),
1
),
VSTACK(
HSTACK(
"Count",
SEQUENCE(
,
COLUMNS(
r
)
)
),
HSTACK(
u,
r
)
)
)Excel solution 2 for Assign Fruits to Tiers, proposed by Bo Rydobon 🇹🇭:
=LET(z,A3:C14,t,TOCOL(z,3),g,GROUPBY(HSTACK(t,TOCOL(IFS(z>0,A2:C2),3)),t,ROWS,,0),PIVOTBY(TAKE(g,,1),DROP(g,,2),INDEX(g,,2),ARRAYTOTEXT,,0,,0))Excel solution 3 for Assign Fruits to Tiers, proposed by Rick Rothstein:
=LET(
u,
SORT(
UNIQUE(
TOCOL(
A3:C14,
3
)
)
),
n,
TRANSPOSE(
COUNTIF(
OFFSET(
A3:A14,
,
{0,
1,
2}
),
u
)
),
s,
SEQUENCE(
,
MAX(
n
)
),
HSTACK(
VSTACK(
"Count",
u
),
SUBSTITUTE(
TRIM(
REDUCE(
s,
SEQUENCE(
MAX(
n
)-1
),
LAMBDA(
a,
x,
VSTACK(
a,
BYCOL(
IF(
CHOOSECOLS(
n,
x
)=s,
TRANSPOSE(
A2:C2
),
" "
),
CONCAT
)
)
)
)
),
" ",
", "
)
)
)Excel solution 4 for Assign Fruits to Tiers, proposed by John V.:
=LET(
r,
A3:C14,
b,
TOCOL(
r,
1
),
f,
TOCOL(
IFS(
r>0,
A2:C2
),
2
),
PIVOTBY(
b,
BYROW(
N(
b&f=TOROW(
b&f
)
),
SUM
),
f,
LAMBDA(
x,
ARRAYTOTEXT(
UNIQUE(
x
)
)
),
,
0,
,
0
)
)Excel solution 5 for Assign Fruits to Tiers, proposed by Kris Jaganah:
=LET(a,TOCOL(A2:C2&"-"&A3:C14),b,GROUPBY(a,a,COUNTA,,0),c,TAKE(b,,1),d,TEXTAFTER(c,"-"),PIVOTBY(d,DROP(b,,1),TEXTSPLIT(c,"-"),ARRAYTOTEXT,,0,,0,,d<>""))Excel solution 6 for Assign Fruits to Tiers, proposed by Julian Poeltl:
=LET(
A,
A3:C14,
F,
A2:C2,
U,
SORT(
UNIQUE(
TOCOL(
A,
3
)
)
),
C,
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
A
)
),
LAMBDA(
B,
C,
HSTACK(
B,
MAP(
U,
LAMBDA(
D,
IFERROR(
ROWS(
FILTER(
INDEX(
A,
,
C
),
INDEX(
A,
,
C
)=D
)
),
0
)
)
)
)
)
),
,
1
),
VSTACK(
HSTACK(
"Count",
SEQUENCE(
,
MAX(
C
)
)
),
HSTACK(
U,
IFERROR(
MAP(
U&SEQUENCE(
,
MAX(
C
)
),
LAMBDA(
A,
TEXTJOIN(
", ",
,
FILTER(
F,
ISNUMBER(
XMATCH(
F&A,
TOROW(
F&U&C
)
)
)
)
)
)
),
""
)
)
)
)Excel solution 7 for Assign Fruits to Tiers, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_hdrs, A2:C2,
_body, A3:C14,
_rept, IFS(_body <> "", _hdrs),
_fbody, TOCOL(_body, 3),
_fhdrs, TOCOL(_rept, 3),
_group, GROUPBY(HSTACK(_fbody, _fhdrs), _fbody, ROWS, 0, 0),
_rtrn, PIVOTBY(TAKE(_group, , 1), TAKE(_group, , -1), INDEX(_group, 0, 2), ARRAYTOTEXT, 0, 0, , 0),
_rtrn
)Excel& solution 8 for Assign Fruits to Tiers, proposed by Timothée BLIOT:
=LET(A,
A3:A11,
B,
B3:B14,
C,
C3:C8,
D,
VSTACK(
IF(
A2=A,
,
A2
),
IF(
B2=B,
,
B2
),
IF(
C2=C,
,
C2
)
),
E,
VSTACK(
A,
B,
C
),
F,
MAP(D,
E,
LAMBDA(x,
y,
SUM(--(FILTER(
E,
D=x
)=y)))),
G,
UNIQUE(
HSTACK(
D,
E,
F
)
),
PIVOTBY(
INDEX(
G,
,
2
),
INDEX(
G,
,
3
),
INDEX(
G,
,
1
),
ARRAYTOTEXT,
,
0,
,
0
))Excel solution 9 for Assign Fruits to Tiers, proposed by Hussein SATOUR:
=LET(v,A3:C14,c,MAP(v,LAMBDA(x,IF(x="",1/0,SUM((INDEX(v,,COLUMN(x))=x)*1)))),PIVOTBY(TOCOL(v,1),TOCOL(c,2),TOCOL(IF(v<>"",A2:C2,1/0),2),LAMBDA(y,ARRAYTOTEXT(UNIQUE(y))),,0,,0))Excel solution 10 for Assign Fruits to Tiers, proposed by Oscar Mendez Roca Farell:
=PIVOTBY(TOCOL(
A3:C14,
1,
1
),
TOCOL(DROP(REDUCE("",
A3:C3,
LAMBDA(i,
x,
LET(f,
TAKE(
x:C14,
,
1
),
HSTACK(i,
(1/COUNTIF(
f,
f
))^-1)))),
,
1),
2,
1),
TOCOL(
IFS(
A3:C14>"",
A2:C2
),
2,
1
),
LAMBDA(
n,
ARRAYTOTEXT(
UNIQUE(
n
)
)
),
,
0,
,
0)Excel solution 11 for Assign Fruits to Tiers, proposed by Duy Tùng:
=LET(
I,
INDEX,
a,
A3:C14,
c,
TOCOL(
IFS(
a>0,
A2:C2
),
3
),
d,
GROUPBY(
HSTACK(
TOCOL(
a,
1
),
c
),
c,
ROWS,
,
0
),
u,
PIVOTBY(
I(
d,
,
1
),
I(
d,
,
3
),
I(
d,
,
2
),
ARRAYTOTEXT,
,
0,
,
0
),
IF(
TAKE(
u,
1
)&TAKE(
u,
,
1
)="",
"Count",
u
)
)Excel solution 12 for Assign Fruits to Tiers, proposed by Sunny Baggu:
=LET(
_a, SORT(UNIQUE(TOCOL(A3:C14, 3))),
_b, DROP(
REDUCE("", _a, LAMBDA(a, v, VSTACK(a, BYCOL(N(A3:C14 = v), LAMBDA(a, SUM(a)))))),
1
),
_c, SEQUENCE(, MAX(_b)),
_d, DROP(
REDUCE(
"",
SEQUENCE(ROWS(_a)),
LAMBDA(x, y,
VSTACK(
x,
MAP(_c, LAMBDA(a, TEXTJOIN(", ", , IF(INDEX(_b, y, ) = a, A2:C2, ""))))
)
)
),
1
),
VSTACK(HSTACK("Count", _c), HSTACK(_a, _d))
)Excel solution 13 for Assign Fruits to Tiers, proposed by LEONARD OCHEA 🇷🇴:
=LET(t,A3:C14,s,TOCOL(t,1),C,INDEX,g,GROUPBY(HSTACK(TOCOL(IF(t>"",A2:C2,z),3),s),s,COUNTA,,0),PIVOTBY(C(g,,2),C(g,,3),C(g,,1),ARRAYTOTEXT,,0,,0))Excel solution 14 for Assign Fruits to Tiers, proposed by Asheesh Pahwa:
=LET(fr,A3:C14,_fr,A2:C2,s,SORT(UNIQUE(TOCOL(fr,1))),d,DROP(REDUCE("",_fr,LAMBDA(x,y,HSTACK(x,LET(I,TOCOL(INDEX(fr,,XMATCH(y,_fr)),1),
DROP(REDUCE("",s,LAMBDA(a,v,VSTACK(a,SUM(--(I=v))))),1))))),,1),c,s&d,
sq,SEQUENCE(,MAX(d)),tc,TOCOL(s&sq),m,MAP(tc,LAMBDA(x,
LET(e,N(x=c),TEXTJOIN(",",1,IF(e,_fr,""))))),HSTACK(VSTACK("Count",s),VSTACK(sq,WRAPROWS(m,5))))Excel solution 15 for Assign Fruits to Tiers, proposed by ferhat CK:
=IFERROR(
LET(
b,
SORT(
UNIQUE(
TOCOL(
A3:C14,
1
)
)
),
q,
LAMBDA(
x,
y,
LET(
j,
MAP(
SEQUENCE(
,
3
),
LAMBDA(
n,
SUM(
N(
INDEX(
A3:C14,
,
n
)=x
)
)
)
),
TEXTJOIN(
", ",
,
"fruits"&FILTER(
SEQUENCE(
,
3
),
j=y
)
)
)
),
r,
MAKEARRAY(
COUNTA(
b
),
5,
LAMBDA(
x,
y,
q(
INDEX(
b,
x
),
y
)
)
),
VSTACK(
HSTACK(
"Count",
SEQUENCE(
,
5
)
),
HSTACK(
b,
r
)
)
),
""
)Excel solution 16 for Assign Fruits to Tiers, proposed by Ankur Sharma:
=LET(FruRa, A3:C14, Fru, SORT(UNIQUE(TOCOL(FruRa, 3))), FruCou, SEQUENCE(, MAX(BYROW(Fru, LAMBDA(z, MAX(BYCOL(FruRa, LAMBDA(y, IFERROR(ROWS(FILTER(y, y = z)), 0)))))))),
TJ, TEXTJOIN,
VSTACK(HSTACK("Count", FruCou), HSTACK(Fru,
TEXTSPLIT(TJ(":", , BYROW(Fru, LAMBDA(z,
TJ(", ", ,
LET(a, BYCOL(FruRa, LAMBDA(y, LET(b, ROWS(FILTER(y, y = z)),
IF(IFERROR(b, 0) = 0, "", b)))),
TJ("-", FALSE, MAP(FruCou, LAMBDA(x, TJ(", ", , FILTER(A2:C2, a = x, "")))))))))),
"-", ":"))))Excel solution 17 for Assign Fruits to Tiers, proposed by Imam Hambali:
=LET(
tc,
TOCOL(
IF(
A3:C14>0,
A3:C14&"-"&A2:C2,
NA()
),
3
),
gb,
GROUPBY(
tc,
tc,
COUNTA,
0,
0
),
PIVOTBY(
TEXTBEFORE(
TAKE(
gb,
,
1
),
"-"
),
TAKE(
gb,
,
-1
),
TEXTAFTER(
TAKE(
gb,
,
1
),
"-"
),
ARRAYTOTEXT,
0,
0,
,
0
)
)Excel solution 18 for Assign Fruits to Tiers, proposed by Andres Rojas Moncada:
=LET(fx,LAMBDA(v,r,ENCOL(SI(v<>"",r,""),,1)),fr,A3:C14,cf,fx(fr,fr),va,fx(fr,A2:C2),PIVOTARPOR(cf,MAP(cf&va,LAMBDA(x,SUMA(--(cf&va=x)))),va,LAMBDA(gr,MATRIZATEXTO(UNICOS(gr))),0,0,,0,,cf<>""))Solving the challenge of Assign Fruits to Tiers with Python
Python solution 1 for Assign Fruits to Tiers, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "566 Count in Columns.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=12)
test = pd.read_excel(path, usecols="E:J", skiprows=1, nrows=4).fillna('')
result = (input.melt(var_name='basket', value_name='fruit')
.dropna()
.groupby(['fruit', 'basket'])
.size()
.reset_index(name='Count')
.pivot_table(index='fruit', columns='Count', values='basket', aggfunc=lambda x: ', '.join(sorted(x)))
.reset_index()
.rename_axis(None, axis=1)
.sort_values(by='fruit')
.reindex(columns=['fruit', 1, 2, 3, 4, 5])
.fillna('')
.rename(columns={'fruit': 'Count'}))
print(result.equals(test)) # True
Solving the challenge of Assign Fruits to Tiers with Python in Excel
Python in Excel solution 1 for Assign Fruits to Tiers, proposed by Alejandro Campos:
df = xl("A2:C14", headers=True)
all_fruits = pd.concat([df[col] for col in df.columns], ignore_index=True).dropna().unique()
def get_fruit_columns(fruit, df):
counts = df.apply(lambda col: col == fruit).sum()
return [', '.join(df.columns[counts == count]) if any(counts == count) else None for count in range(1, 6)]
result = pd.DataFrame([get_fruit_columns(fruit, df) for fruit in all_fruits], index=all_fruits, columns=range(1, 6))
result.reset_index(inplace=True)
result.rename(columns={'index': 'Count'}, inplace=True)
result.sort_values(by='Count', inplace=True)
result.reset_index(drop=True, inplace=True)
result.fillna(' ')
Solving the challenge of Assign Fruits to Tiers with R
R solution 1 for Assign Fruits to Tiers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/566 Count in Columns.xlsx"
input = read_excel(path, range = "A2:C14")
test = read_excel(path, range = "E2:J6")
result = input %>%
pivot_longer(everything(), names_to = "basket", values_to = "fruit") %>%
summarise(Count = n(), .by = c(fruit, basket)) %>%
na.omit() %>%
pivot_wider(names_from = Count,
values_from = basket,
values_fn = list(basket = ~ str_c(sort(.x), collapse = ", "))) %>%
arrange(fruit) %>%
select(Count = fruit,`1`, `2`, `3`, `4`, `5`)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&&