如何找出A列上下行间不同的数

crystalbabe561

求助各位大神，EXECL表里A列有各种数字，我的任务是找出每上下两个单元格中不同的数。我写了两种公式，结果都不对，麻烦大神们帮我看下到底公式应该怎么写才对。

良良

F2

1. =TEXTJOIN(",",1,IF(MMULT(N(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)*ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",))+1))-LEN(A2)+1,LEN(A2)))=TRANSPOSE(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",LEN(A3))),LEN(A3)*ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))-LEN(A3)+1,LEN(A3))))),ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))^0),"",TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)*ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",))+1))-LEN(A2)+1,LEN(A2)))))

复制代码

G2

1. =TEXTJOIN(",",1,IF(MMULT(N(TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",LEN(A3))),LEN(A3)*ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))-LEN(A3)+1,LEN(A3)))=TRANSPOSE(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),LEN(A2)*ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",))+1))-LEN(A2)+1,LEN(A2))))),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",))+1))^0),"",TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",LEN(A3))),LEN(A3)*ROW(INDIRECT("1:"&LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1))-LEN(A3)+1,LEN(A3)))))

复制代码

数组三键，下拉。

crystalbabe561

良良 发表于 2023-4-11 12:43
F2
G2
数组三键，下拉。

大神，请收下我的膝盖！ 困扰了我三天的公式就这么解决了，真的是非常感谢！