求一个类似于补集的公式

飞越一切

新建 Microsoft Office Excel 工作表.rar (8.76 KB, 下载次数: 13)

2023-6-19 10:37 上传

点击文件名下载附件

无聊的黑猫

=TEXTJOIN(",",1,IF(1-ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),ROW($1:$50)*99-98,99)),C2)),TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),ROW($1:$50)*99-98,99)),""))三键

xlllw203

1. =TEXTJOIN(",",,UNIQUE(TEXTSPLIT(TEXTJOIN(",",,B2,C2),,","),,1))

复制代码

1. =TEXTJOIN(",",,TEXT(IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(B2&","&C2,",","</b><b>")&"</b></a>","a/b[not(preceding::* =.or following::* =.)]"),""),"00;;;"))

复制代码

满坛皆为吾师

低版本，仅支持7个数，D2，数组三键下拉

1. =SUBSTITUTE(TEXT(SUM(SMALL((COUNTIF(B2:C2,TEXT(ROW($1:$36),"!*00!*"))=1)*ROW($1:$36),ROW($30:$36))*10^((7-ROW($1:$7))*2)),REPT("00!,",7)),"00,",)

复制代码

飞越一切

满坛皆为吾师 发表于 2023-6-19 10:49
低版本，仅支持7个数，D2，数组三键下拉

大神，能搞个VBA不，这7个数有点少啊

飞越一切

xlllw203 发表于 2023-6-19 10:48

大神，是不是我的版本低，没结果，能搞个VBA 不

飞越一切

无聊的黑猫 发表于 2023-6-19 10:45
=TEXTJOIN(",",1,IF(1-ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),ROW($1:$50)*99-98,99)) ...

大神，是不是我的版本低，没结果，能搞个VBA 不

jivy1212

1. =MID(SUBSTITUTE(TEXT(MID(NPV(99,IF(ISERR(FIND(MID(B2,ROW($1:$66)*3-2,2),C2)),MID(B2,ROW($1:$66)*3-2,2)*1)),3,14),REPT("!,00",7)),",00",),2,14)

复制代码

限7个数

袁勇

飞越一切

袁勇 发表于 2023-6-19 11:11

大神给个附件啊，我不会VBA啊