Provide a formula to list numbers which appear more than once in Range A2:A20 and have both B and C in B2:B20. 2, 4 & 8 appear more than once. But 2 has only C whereas 4 & 8 have both B & C. Hence answer would be 4, 8.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 6
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Numbers Appearing Repeatedly with Power Query
Power Query solution 1 for List Numbers Appearing Repeatedly, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calculation = Table.Group(
Source,
{"Data"},
{{"Calculation", each List.Count([Alpha]) > 2 and List.ContainsAll([Alpha], {"B", "C"})}}
),
Filtered = Table.SelectRows(Calculation, each ([Calculation] = true))[Data],
Final = Text.Combine(List.Transform(Filtered, Text.From), ", ")
in
FinalSolving the challenge of List Numbers Appearing Repeatedly with Excel
Excel solution 1 for List Numbers Appearing Repeatedly, proposed by Rick Rothstein:
=TEXTJOIN(", ",1,UNIQUE(LET(R,A2:A20,T,CONCAT(" "&R&OFFSET(R,,1)&" "),IF(ISNUMBER(FIND(" "&R&"B ",T)*FIND(" "&R&"C ",T)),R,""))))Excel solution 2 for List Numbers Appearing Repeatedly, proposed by 🇰🇷 Taeyong Shin:
=ARRAYTOTEXT(
UNIQUE(
FILTER(
A2:A20,
MMULT(
SIGN(
COUNTIFS(
A2:A20,
A2:A20,
B2:B20,
{"B",
"C"}
)
),
TOCOL(
N(
+A2:B2
)+1
)
)=2
)
)
)Excel solution 3 for List Numbers Appearing Repeatedly, proposed by Julian Poeltl:
=LET(
D,
A2:A20,
A,
B2:B20,
B,
D&A,
U,
UNIQUE(
D
),
M,
FILTER(
U,
COUNTIF(
D,
U
)>1
),
TEXTJOIN(
", ",
,
FILTER(
M,
IFERROR(
MAP(
M,
LAMBDA(
A,
ROWS(
FILTER(
B,
B=A&"B"
)
)*ROWS(
FILTER(
B,
B=A&"C"
)
)
)
),
0
)
)
)
)Excel solution 4 for List Numbers Appearing Repeatedly, proposed by Timothée BLIOT:
=UNIQUE(FILTER($A$2:$A$20,
(COUNTIF(
$A$2:$A$20,
$A$2:$A$20
)>1) *
(COUNTIFS(
$A$2:$A$20,
$A$2:$A$20,
$B$2:$B$20,
"B"
) > 0) *(COUNTIFS(
$A$2:$A$20,
$A$2:$A$20,
$B$2:$B$20,
"C"
) > 0),
"No value"))Excel solution 5 for List Numbers Appearing Repeatedly, proposed by Luan Rodrigues:
=UNIQUE(FILTER(A2:B20,(B2:B20<>"A")*(B2:B20="B")))Excel solution 6 for List Numbers Appearing Repeatedly, proposed by Bhavya Gupta:
=LET(
Data,
A2:A20,
Alpha,
B2:B20,
Criteria,
{"B",
"C"},
UNIQUE(
FILTER(
Data,
BYROW(
NOT(
ISNA(
XLOOKUP(
Data&Criteria,
Data&Alpha,
Data
)
)
),
LAMBDA(
x,
PRODUCT(
--x
)
)
)
)
)
)Excel solution 7 for List Numbers Appearing Repeatedly, proposed by Bhavya Gupta:
=LET(Data,
A2:A20,
Alpha,
B2:B20,
UNIQUE(FILTER(Data,
MAP(Data,
LAMBDA(a,
(SUM(--(MAP(
UNIQUE(
FILTER(
Alpha,
Data=a
)
),
LAMBDA(
x,
OR(
x={"B",
"C"}
)
)
)))=2)*(SUM(--(Data=a))>1))))))Excel solution 8 for List Numbers Appearing Repeatedly, proposed by Charles Roldan:
=LET(
Data,
A2:A20,
Alpha,
B2:B20,
Criteria,
{"B",
"C"},
ARRAYTOTEXT(
UNIQUE(
FILTER(
Data,
BYROW(
ISNUMBER(
XMATCH(
Data & Criteria,
Data & Alpha
)
),
LAMBDA(
x,
AND(
x
)
)
)
)
)
)
)Excel solution 9 for List Numbers Appearing Repeatedly, proposed by Charles Roldan:
=LET(
Data, A2:A20,
Alpha, B2:B20,
a, DROP(PIVOTBY(Data, Alpha, Alpha, COUNTA, , 0, , 0), 1),
FILTER(TAKE(a, , 1), BYROW(N(DROP(a, , 1)), LAMBDA(x, AND(x >= {0, 1, 1}))))
)Excel solution 10 for List Numbers Appearing Repeatedly, proposed by CA Raghunath Gundi:
=UNIQUE(TAKE(FILTER(A2:B20,COUNTIFS(A2:A20,A2:A20,B2:B20,"B")*COUNTIFS(A2:A20,A2:A20,B2:B20,"C")),,1))Excel solution 11 for List Numbers Appearing Repeatedly, proposed by Jardiel Euflázio:
=LET(
a,
UNIQUE(
A2:B20
),
b,
FILTER(a,
(INDEX(
a,
,
2
)="B")+(INDEX(
a,
,
2
)="C")),
c,
INDEX(
b,
,
1
),
TEXTJOIN(
", ",
,
FILTER(
c,
MATCH(
c,
c,
0
)<>SEQUENCE(
ROWS(
c
)
)
)
)
)Excel solution 12 for List Numbers Appearing Repeatedly, proposed by Jardiel Euflázio:
=TEXTJOIN(
",",
,
UNIQUE(
FILTER(
A2:A20,
COUNTIFS(A2:A20,A2:A20,B2:B20,"B")*
COUNTIFS(A2:A20,A2:A20,B2:B20,"C")
)
)
)Excel solution 13 for List Numbers Appearing Repeatedly, proposed by Cary Ballard, DML:
=LET(
a, A2:A19,
b, B2:B19,
UNIQUE(FILTER(a, COUNTIFS(a, a, b, "B") * COUNTIFS(a, a, b, "c")))
)Excel solution 14 for List Numbers Appearing Repeatedly, proposed by Rajesh Sinha:
=UNIQUE(
FILTER(
A2:A20,
B2:B20="B"
)
)
or
=UNIQUE(
FILTER(
A2:A20,
COUNTIFS(
A2:A20,
A2:A20,
B2:B20,
"B"
)*COUNTIFS(
A2:A20,
A2:A20,
B2:B20,
"C"
)
)
)Excel solution 15 for List Numbers Appearing Repeatedly, proposed by Rajesh Sinha:
=IFERROR(
INDEX(
A2:A20,
MATCH(
0,
COUNTIF(
C1:$C$1,
A2:A20
)+IF(
COUNTIF(
A2:A20,
A2:A20
)>1,
0,
1
),
0
)
),
""
)}
N.B. Finish with Crrl+Shift+Enter then fill down & Right Formula with adjust cell reference for Duplicates in column B.
Other is Dynamic array combination of UNIQUE & FILTER in F2.
=UNIQUE(
FILTER(
A2:A20,
COUNTIF(
A2:A20,
A2:A20
)>1,
""
),
FALSE
)Excel solution 16 for List Numbers Appearing Repeatedly, proposed by Rajesh Sinha:
=UNIQUE(FILTER(A2:A20,COUNTIFS(A2:A20,A2:A20,B2:B14,"B")*COUNTIFS(A2:A20,A2:A20,B2:B20,"C")))
or
=UNIQUE(FILTER(A2:A20,B2:B20="B"))Excel solution 17 for List Numbers Appearing Repeatedly, proposed by red craven:
=LET(
f,
LAMBDA(
x,
UNIQUE(
FILTER(
A2:A20,
B2:B20=x
)
)
),
b,
f(
"B"
),
TEXTJOIN(
", ",
1,
XLOOKUP(
f(
"C"
),
b,
b,
""
)
)
)Solving the challenge of List Numbers Appearing Repeatedly with Python in Excel
Python in Excel solution 1 for List Numbers Appearing Repeatedly, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:B20", True)
group = data.groupby("Data").agg(Count=("Data", "size"), Alpha=("Alpha", list))
filter = group[
(group["Count"] > 1) & group["Alpha"].map(lambda x: {"B", "C"}.issubset(set(x)))
]
result = filter.index.values
Solving the challenge of List Numbers Appearing Repeatedly with DAX
DAX solution 1 for List Numbers Appearing Repeatedly, proposed by Zoran Milokanović:
DEFINE
MEASURE Input[AppearanceB] = SUMX(Input, IF(Input[Alpha] = "B", 1, 0))
MEASURE Input[AppearanceC] = SUMX(Input, IF(Input[Alpha] = "C", 1, 0))
MEASURE Input[Appearance] = COUNTROWS(Input)
EVALUATE
{
CONCATENATEX(
FILTER(
SUMMARIZECOLUMNS(
Input[Data],
"Total", [Appearance],
"B", [AppearanceB],
"C", [AppearanceC]
),
[Total] > 1 && [B] > 0 && [C]
),
Input[Data],
", "
)
}
&&&