Provide a formula to generate the list of animals who are common in all 3 columns. Common animals have been highlighted in color.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 13
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Common Animals with Power Query
Power Query solution 1 for Find Common Animals, proposed by Brian Julius:
let
Source = #"Animals Raw",
Intersection = List.Intersect(Table.ToColumns(Source))
in
IntersectionPower Query solution 2 for Find Common Animals, proposed by Cristian Angyal:
let
Source = List.Intersect(Table.ToColumns(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]))
in
SourceSolving the challenge of Find Common Animals with Excel
Excel solution 1 for Find Common Animals, proposed by Rick Rothstein:
=filter(a2:a11,iferror(match(a2:a11,b2:b11,)*match(a2:a11,c2:c11,),))Excel solution 2 for Find Common Animals, proposed by Rick Rothstein:
=filter(a2:a11,iferror(match(a2:a11,b2:b11,)*match(a2:a11,c2:c11,),))Excel solution 3 for Find Common Animals, proposed by Rick Rothstein:
=filter(a2:a11,isnumber(match(a2:a11,b2:b11,)*match(a2:a11,c2:c11,)))Excel solution 4 for Find Common Animals, proposed by Rick Rothstein:
=let(r,a2:a10,a,filter(r,r<>0),sum(index(a,sequence(3,,rows(a)-2))))Excel solution 5 for Find Common Animals, proposed by محمد حلمي:
=LET(
A,MID(UNIQUE(TOCOL(COLUMN(A2:C2)&A2:C12,,1)),2,99),
V,UNIQUE(A),
R,MAP(V,LAMBDA(X,SUM(--(X=A)))),
FILTER(V,R=3))Excel solution 6 for Find Common Animals, proposed by محمد حلمي:
=LET(a;A2:A12;FILTER(a;COUNTIFS(B2:B12;a)+COUNTIFS(C2:C12;a)>1))Excel solution 7 for Find Common Animals, proposed by 🇰🇷 Taeyong Shin:
=LET(d,A2:C12,c,COLUMN(d),g,GROUPBY(TOCOL(d,,1),TOCOL(c+N(+d),,1),CONCAT,,0),FILTER(TAKE(g,,1),DROP(g,,1)=CONCAT(c)))Excel solution 8 for Find Common Animals, proposed by 🇰🇷 Taeyong Shin:
=LET(
Animals, A2:A12,
Compare, B2:C12,
bool, IFNA(REDUCE(1, SEQUENCE(COLUMNS(Compare)), LAMBDA(a,b, a * XMATCH(Animals, INDEX(Compare, , b)))), ),
FILTER(Animals, bool)
)
=FILTER(A2:A12, REDUCE(1, SEQUENCE(COLUMNS(B2:C12)), LAMBDA(a,n, a * COUNTIF(INDEX(B2:C12, , n), A2:A12) )) )Excel solution 9 for Find Common Animals, proposed by Julian Poeltl:
=LET(T,A2:C12,F,TAKE(T,,1),B,BYCOL(T,LAMBDA(A,CONCAT(A))),S,LEN(B)-LEN(SUBSTITUTE(B,F,"")),FILTER(F,BYROW(S,LAMBDA(A,MIN(A)))>0))Excel solution 10 for Find Common Animals, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_rng,
A2:C12,
_unq,
UNIQUE(TOCOL(_rng)),
_calc,
MAP(
_unq,
LAMBDA(a,
SUM(--ISNUMBER(
FIND(a, BYCOL(_rng, LAMBDA(b, TEXTJOIN("-", TRUE, b))))))))
= COLUMNS(_rng),
FILTER(_unq, _calc))Excel solution 11 for Find Common Animals, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_rng,
A2:C12,
_unq,
UNIQUE(TOCOL(_rng)),
_calc,
MAP(
_unq,
LAMBDA(
a,
SUM(--(MMULT(
--TRANSPOSE(_rng = a),
SEQUENCE(ROWS(_rng)) ^ 0) > 0))
= COLUMNS(_rng))),
FILTER(_unq, _calc))Excel solution 12 for Find Common Animals, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A12,
COUNTIFS(B2:B12, A2:A12)
+ COUNTIFS(C2:C12, A2:A12)
= 2)
Missed one shot earlier. The correct formula will be:
= FILTER(
A2:A12,
(COUNTIFS(B2:B12, A2:A12) > 0)
+ (COUNTIFS(C2:C12, A2:A12) > 0) = 2)Excel solution 13 for Find Common Animals, proposed by Hussein SATOUR:
=LET(a, A2:A12, b, TEXTJOIN("",,B2:B12), c, TEXTJOIN("",,C2:C12),
FILTER(a, ISNUMBER(SEARCH(a, b) + SEARCH(a, c))))Excel solution 14 for Find Common Animals, proposed by Abdallah Ally:
=FILTER(A2:A12,(XLOOKUP(A2:A12,B2:B12,B2:B12,"")<>"")*(XLOOKUP(A2:A12,C2:C12,C2:C12,"")<>""))Excel solution 15 for Find Common Animals, proposed by Bhavya Gupta:
=LET(rng,A2:C12,UNIQUE(TOCOL(MAP(rng,LAMBDA(a,IF(PRODUCT(BYCOL(rng=a,LAMBDA(x,SUM(--x))))>0,a,NA()))),3,TRUE)))Excel solution 16 for Find Common Animals, proposed by Bhavya Gupta:
=LET(rng,A2:C12,r,UNIQUE(TOCOL(rng,,TRUE)),FILTER(r,MAP(r,LAMBDA(a,PRODUCT(BYCOL(rng=a,LAMBDA(x,SUM(--x))))))>0))Excel solution 17 for Find Common Animals, proposed by Charles Roldan:
=LET(FilterBy, LAMBDA(x, LAMBDA(y, FILTER(y, MAP(y, x)))),
MatchWith, LAMBDA(x, LAMBDA(y, XMATCH(x, y))), LAMBDA(Data,
FilterBy(LAMBDA(List, IFERROR(AND(BYCOL(Data, MatchWith(List))), )))(UNIQUE(TOCOL(Data, , 1)))))(A2:C12)Excel solution 18 for Find Common Animals, proposed by Owen Price:
=LET(
d,$A$2:$C$12,
m,LAMBDA(x,y,XLOOKUP(x,y,y)),
x,m(m(INDEX(d,,1),INDEX(d,,2)),INDEX(d,,3)),
FILTER(x,NOT(ISERROR(x)))
)Excel solution 19 for Find Common Animals, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=IFERROR(INDEX(A:A;IF(COUNTIF($C$2:$C$12;IFERROR(INDEX(A:A;IF(COUNTIF($B$2:$B$12;A2)=1;ROW();""));""))=1;ROW();""));"")Excel solution 20 for Find Common Animals, proposed by CA Raghunath Gundi:
=FILTER(A2:A12,(COUNTIFS(A2:A12,A2:A12)*COUNTIFS(B2:B12,A2:A12)*COUNTIFS(C2:C12,A2:A12))=1)Excel solution 21 for Find Common Animals, proposed by Jardiel Euflázio:
=LET(
a,TEXTJOIN("-",,A2:C12),
b,TEXTSPLIT(a,,"-"),
c,UNIQUE(b),
FILTER(
c,COUNTIF(A2:A12,c)*
COUNTIF(B2:B12,c)*
COUNTIF(C2:C12,c)
)
)Excel solution 22 for Find Common Animals, proposed by Victor Momoh (MVP, MOS, R.Eng):
=FILTER(A2:A12,BYROW(A2:A12,LAMBDA(x,COUNTIF(B2:C12,x)))=2)Excel solution 23 for Find Common Animals, proposed by Sergei Baklan:
=LET(
both, LAMBDA( a, b,
LET(
ab, VSTACK( UNIQUE(a), UNIQUE(b) ),
UNIQUE( VSTACK( UNIQUE(ab,,1), UNIQUE(ab) ), , 1)
) ),
both( both( Animals1, Animals2 ), Animals3 )
)Excel solution 24 for Find Common Animals, proposed by Cary Ballard, DML:
=LET(
a, A2:C12,
b, TOCOL(a, , 1),
UNIQUE(TOCOL(IFS(MAP(b, LAMBDA(m, AND(SORT(UNIQUE(FILTER(INT(SEQUENCE(ROWS(b), , , 1 / ROWS(a))), m = b))) = SEQUENCE(COLUMNS(a))))), b), 2))
)Excel solution 25 for Find Common Animals, proposed by Cary Ballard, DML:
=LET(
a, A2:C12,
b, TOCOL(a, , 1),
c, INT(SEQUENCE(ROWS(b), , , 1 / ROWS(a))),
UNIQUE(FILTER(b, MAP(b, LAMBDA(m, SUM(UNIQUE(FILTER(c, m = b))) = 6))))
)Excel solution 26 for Find Common Animals, proposed by Viswanathan M B:
=LET(Rng, A1:C20,
RemoveDuplicate, REDUCE(INDEX(Rng,0,1),
SEQUENCE(COLUMNS(E21:G24)-1,1,2),
LAMBDA(x,y, HSTACK(x, UNIQUE(INDEX(Rng,0,y))))),
StackedRaw, TOCOL(RemoveDuplicate),
Stacked, FILTER(StackedRaw, NOT(ISNA(StackedRaw))),
Check, --(Stacked=TRANSPOSE(Stacked)),
Counts, MMULT(Check, SEQUENCE(COLUMNS(Check),1,1,0)),
Return, UNIQUE(FILTER(Stacked, Counts = COLUMNS(Rng))),
Return)Excel solution 27 for Find Common Animals, proposed by Viswanathan M B:
=LET(Stacked, VSTACK(A2:A12,B2:B12,C2:C12),
Check, --(Stacked = TRANSPOSE(Stacked)),
Count, MMULT(Check, SEQUENCE(COLUMNS(Check),1,1,0)),
UNIQUE(FILTER(Stacked, Count=3)))Excel solution 28 for Find Common Animals, proposed by Miguel Angel Franco García:
=ENCOL(BUSCARX(BUSCARX(A2:A12;$B$2:$B$12;$B$2:$B$12);$C$2:$C$12;$C$2:$C$12);3)Excel solution 29 for Find Common Animals, proposed by Riley Johnson:
=LET(
data, DROP( tbl[hashtag#Data], -1 ),
animals, UNIQUE( TOCOL( data ) ),
COL_SEARCH, LAMBDA(animal,
LET(
found?, BYCOL( data, LAMBDA(col,
ISNUMBER( XMATCH( animal, col, 0) )
)
),
AND(found?)
)
),
include_array, MAP( animals, LAMBDA(animal, COL_SEARCH(animal))),
FILTER( animals, include_array, "")
)Excel solution 30 for Find Common Animals, proposed by Paul Martin:
=LET(a, IF(ISNUMBER(XMATCH(A2:A12, B2:B12)), A2:A12, ""),
b, IF(ISNUMBER(XMATCH(a, C2:C12)), a, ""), FILTER(b, b<>""))Excel solution 31 for Find Common Animals, proposed by Najam Ul Hassan:
=IF(COUNTIF($A$2:$A$12,$A2)*COUNTIF($B$2:$B$12,$A2)*COUNTIF($C$2:$C$12,$A2)=1,A2,"")Solving the challenge of Find Common Animals with Python in Excel
Python in Excel solution 1 for Find Common Animals, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:C12", True)
cols = data.T.values.tolist()
result = set(cols[0]).intersection(*cols[1:])
result
Solving the challenge of Find Common Animals with DAX
DAX solution 1 for Find Common Animals, proposed by Zoran Milokanović:
EVALUATE
INTERSECT(
INTERSECT(
SELECTCOLUMNS(Input, "Answer Expected", Input[Animals1]),
SELECTCOLUMNS(Input, "Answer Expected", Input[Animals2])
),
SELECTCOLUMNS(Input, "Answer Expected", Input[Animals3])
)