checking for duplicates per column in a dynamic excel array-CodePudding

I'm pretty new to this forum so please let me know if my question is unclear or if you have other suggestions! :)

I'm trying to generate a formula in excel that evaluates the presence of duplicates in a dynamic array per column and then returns a new 1-dimensional array that contains either True or False when duplicates are present/absent. See the simplified example below:

example array:

{a,b,c,d;
 d,e,f,g;
 a,h,i,j}

The output of the formula should result in {True,False,False,False}.

I have been stuck on this seemingly simple problem for some hours already, so hopefully you will be able to help me!

CodePudding user response：

Seemingly simple, agreed, though until Microsoft release functions such as BYCOL it's anything but, assuming you're wanting to obtain your array output using a single formula.

One option would be:

=LET(ρ,A1:D3,κ,ROWS(ρ),ε,COLUMNS(ρ),η,SEQUENCE(κ*ε,,0),γ,MATCH(ρ,INDEX(ρ,1 MOD(η,κ),1 QUOTIENT(η,κ)),0) SEQUENCE(,ε)/10^6,MMULT(SEQUENCE(,κ),N(INDEX(FREQUENCY(γ,γ)=0,SEQUENCE(κ,ε))))>0)

Replace A1:D3 as required.

Hopefully someone will come along with an improvement.

CodePudding user response：

If you'd have BYCOL() you can use something like:

=BYCOL({a,b,c,d;d,e,f,g;a,h,i,j},LAMBDA(x,COUNTA(UNIQUE(x))<>ROWS(x)))