Question

Hi I have a long array with case numbers that look like this

这些阵列是从一个有公式的表格中选取的,使用公式找到。 I ve在名称管理人中定义的房地产

我想能够看一看这个阵列(完全位于B2室),并返回以下阵列:

123456 
154670

as the elements in the array that have duplicate values

如果我将其分为三个部分,第一部分是阵列的公式,则我可以这样做。

=N(COUNTIF($B$2#, $B2)>1)

copied down the length of the original array, generating a list of numbers based on hoow many times the original values are located in the array and finishing it off with the formula

= unique(filter(A2#, B2:B267=1))

但是,我基本上接受公式内超负荷计算的制约。

I ve tried to do step 2 through something along the lines of

= countif(FindNumber, index(FindNumber, 1))

To at least try to find it for the first number, with maybe being able to copy it down, but I have hit a wall. I am not looking for a VBA solution.

Answer 1

利用TEXTSPLIT在运输回程上进行分班,然后获得一个独特的清单,并重新核对如果不止一次。之后,Join又回到一起。

=LET(
    t,TEXTSPLIT(B2,,CHAR(10)),
    unq,UNIQUE(t),
    TEXTJOIN(CHAR(10),TRUE,REDUCE("",unq,LAMBDA(z,y,IF(SUMPRODUCT(--(t=y))>1,VSTACK(z,y),z)))))

如果通过<代码>完全位于B2中,这意味着它是一种可溢出的公式,而不是B2中的所有数值,那么我们可以简化公式:

=LET(
    t,B2#,
    unq,UNIQUE(t),
    REDUCE(,unq,LAMBDA(z,y,IF(SUMPRODUCT(--(t=y))>1,VSTACK(z,y),z))))

Answer 2

=UNIQUE(FILTER(B2#, COUNTIFS(B2#, B2#) > 1))

友情链接