=IF(COUNTIF(B$1:E$100,ROW())=4,ROW(),"")
and copy down as long as needs.= BYCOL(data, LAMBDA(dataCol, COUNTIFS(dataCol, v)))
TestValueλ
= LAMBDA(value,
AND(BYCOL(data, LAMBDA(dataCol, COUNTIFS(dataCol, value))))
);
= SORT(UNIQUE(TOCOL(data)))
= LET(
distinct, SORT(UNIQUE(TOCOL(data))),
common?, MAP(distinct, LAMBDA(v, TestValueλ(v))),
FILTER(distinct, common?)
)
It works perfectly. Thank you very muchexoterikos
eg cells E1=IF(COUNTIF(B$1:E$100,ROW())=4,ROW(),"")
and copy down as long as needs.
set 100 as Your data needs
Ps It's a challenge to test with Your picture - it's much better to test with an Excel-file.
It works fine. Thank you very muchAs mentioned by the other post, the OP used Excel 2019,
Here is a formula solution,
In F1, array ("Ctrl+Shift+Enter") formula copied down :
=IFERROR(AGGREGATE(15,6,IF(MATCH(A$1:A$23,B$1:B$23,0)+MATCH(A$1:A$23,C$1:C$23,0)+MATCH(A$1:A$23,D$1:D$23,0),A$1:A$23,""),ROW(A1)),"")
View attachment 80326
It works fine. Thank you very much
So informative !!!!!! Thank you so much !!!!!I give you a copy of file to show how does my formula work
Please see the attachment and download to you computer.
bosco_yip
Try to put a number 4 times in just one column and test. Your solution does not appear to work (checking presence in all 4 columns part).Hi,
In F1, formula copied down.
IFERROR(AGGREGATE(15,6,(1/(INDEX(COUNTIF($A$1:$D$23,$A$1:$D$23),,1)=4))*$A$1:$A$23,ROW(A1)),"")
David
I just posted my response from academic interest. You probably don't need a sample. This solution is correct. Thanks.Hi shrivallabha,
Also check this version,
=IFERROR(AGGREGATE(15,6,(1/((COUNTIF(B1:D23,A1:A23))=3))*A1:A23,ROW(A1)),"")
David