两列数字对比取重复数

jassur76 · 发表于 2024-5-13 11:54

两列数字对比取重复数

solextrade · 发表于 2024-5-13 11:58

=MID(NPV(9,IF(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A2:B2)),{1;1})=2,ROW($1:$10)-1)),3,9)

yellowhawk · 发表于 2024-5-13 12:07

本帖最后由 yellowhawk 于 2024-5-13 12:09 编辑

=TEXTJOIN(,,IFERROR(MID(A2,FIND(MID(B2,{1,2,3},1),A2),1),""))
wps最新版本测试正确，如果B列超过3位数，则{1，2，3}增加相应的位数即可。

13570449347 · 发表于 2024-5-13 12:10

solextrade 发表于 2024-5-13 11:58
=MID(NPV(9,IF(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A2:B2)),{1;1})=2,ROW($1:$10)-1)),3,9)

老师能解释一下这逻辑学习下吗？有点没看懂

boyuanoffice · 发表于 2024-5-13 12:28

本帖最后由 boyuanoffice 于 2024-5-13 12:31 编辑

=CONCAT(IF(MID(B2,ROW($1:$15),1)=MID(A2,COLUMN(A:N),1),MID(B2,ROW($1:$15),1),""))

复制代码

jivy1212 · 发表于 2024-5-13 12:47

=MID(NPV(9,MODE.MULT(-MID(A2:B2,{1;2;3},1))),4,9)

复制代码

未考虑只有0等特殊情况。

jisijie630 · 发表于 2024-5-13 14:03

=CONCAT(IF(MMULT(IFERROR(1^FIND(ROW($1:$10)-1,A2:B2),),{1;1})=2,ROW($1:$10)-1,""))

mykitami · 发表于 2024-5-13 14:17

=CONCAT(SORT(IF(ISNUMBER(FIND(MID(B2,SEQUENCE(3),1),A2)),MID(B2,SEQUENCE(3),1),"")))

wlc5201130 · 发表于 2024-5-13 14:53

=CONCAT(IF(LEN(A2&B2)-LEN(SUBSTITUTE(A2&B2,ROW($1:$10)-1,))>1,ROW($1:$10)-1,))

复制代码

zhangcheng6688 · 发表于 2024-5-14 13:01

学习一下字典，打扰了
单元格格式设置成文本
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
For j = 1 To Len(arr(i, 1))
   s = Mid(arr(i, 1), j, 1)
   d(s) = ""
Next
For k = 1 To Len(arr(i, 2))
   s = Mid(arr(i, 2), k, 1)
   If d.exists(s) Then
      arr(i, 3) = arr(i, 3) & "," & s
   End If
Next
d.RemoveAll
Next

For i = 2 To UBound(arr)
s = Mid(arr(i, 3), 2)
brr = Split(s, ",")
For j = 0 To UBound(brr)
   For k = j + 1 To UBound(brr)
      If brr(j) > brr(k) Then
         t = brr(j): brr(j) = brr(k): brr(k) = t
      End If
   Next
Next
For l = 0 To UBound(brr)
   s1 = s1 & brr(l)
Next
arr(i, 3) = s1
s1 = ""
Next
[a1].CurrentRegion = arr

		自动登录	找回密码
密码			免费注册

[求助] 两列数字对比取重复数

评分