=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
I give you a copy of file to show how does my formula workIt 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