代码之家 › 专栏 › 技术社区 › ChingLu Tay

从一个单元格中的组合数据中删除重复项

excel-formula excel

ChingLu Tay · 技术社区 · 6 年前

单元格1:abc&def

***更正-抱歉,如果我输入的细节不好。数据实际上是名称,例如:

3 回复 | 直到 6 年前

JvdV 6 年前

也许这会有帮助,直到公式出现。复制粘贴到模块:

str1=替换(str1,“&”,,,) str2=替换(str2,“&”,,,) arr1()=拆分(str1,“,” Y=0 redim preserve arr3(y)保留下一个X redim preserve arr3(y)保留 Y=Y+1 对于x=lbound(arr3)to ubound(arr3) 如果POS<gt;0,则下一个X getString=strReverse(replace(strReverse(getString),strReverse(“,”),strReverse(“&”),1))

=GetString(Cell1, Cell2)

结果:

Pawel Czyz 6 年前

Tom Sharpe 6 年前

按公式计算是可行的。基本方法是:

使用标准的split公式,从 here. but modified so it products an array of strings.

。

使用textjoin连接其余的元素。

0) <row(z1:index(z:z,len(a1)-len(substitute(a1,“&”,“))+1)),“”, trim(mid(substitute(a1,“&”,rept(“”,len(a1)),(row(z1:index(z:z,len(a1)-len(substitute(a1,“&”,“))+1)))*len(a1)+1,len(a1))))


    
但由于有两个单元格,分隔符可以是“,”或“&”,因此每个A1都将成为

substitute(a1&“,&a2,”&“,,”)

要获得最终结果,“”作为“&”,需要执行以下操作:

=substitute(*整个公式*,,,,,,&“,len(*整个公式*)-len(substitute(*整个公式*,,,,,“))

所以唯一理智的方法就是用几个辅助细胞-

在B1中:

=substitute(a1&“”,&a2,“&”,,“)

在C1中:

=textjoin(“,”,true,if(match)(
trim(mid(substitute(b1,“,”,rept(“”,len(b1)),(row($z1:index($z:$z,len(b1)-len(substitute(b1,“,”“))+1)×len(b1)+1,len(b1))),
trim(mid(substitute(b1,“,”,rept(“”,len(b1)),(row($z1:index($z:$z,len(b1)-len(substitute(b1,“,”“))+1)×len(b1)+1,len(b1))),
0)
<row($z1:index($z$z,len(b1)-len(substitute(b1,“,,”“))+1)),”,








但经过修改后,它会生成一个字符串数组。


   
    
     
      
       
        
         
          
           
           
           =TEXTJOIN(",",true,IF(MATCH(
TRIM(MID(SUBSTITUTE(A1,"&",REPT(" ",LEN(A1))),(ROW(Z1:INDEX(Z:Z,LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))+1))-1)*LEN(A1)+1,LEN(A1))),
TRIM(MID(SUBSTITUTE(A1,"&",REPT(" ",LEN(A1))),(ROW(Z1:INDEX(Z:Z,LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))+1))-1)*LEN(A1)+1,LEN(A1))),
0)
<ROW(Z1:INDEX(Z:Z,LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))+1)),"",
TRIM(MID(SUBSTITUTE(A1,"&",REPT(" ",LEN(A1))),(ROW(Z1:INDEX(Z:Z,LEN(A1)-LEN(SUBSTITUTE(A1,"&",""))+1))-1)*LEN(A1)+1,LEN(A1)))))

           
           
           substitute(A1&","&A2,"&",",")

           
           
           =substitute (*entire formula*,",","&",len(*entire formula*)-len(substitute(*entire formula*,",","")))

           
            所以唯一理智的方法就是用几个辅助细胞-
           
           
           
           =SUBSTITUTE(A1&" , "&A2,"&",",")

           
            在C1中:
           
           =TEXTJOIN(" , ",true,IF(MATCH(
TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",LEN(B1))),(ROW($Z1:INDEX($Z:$Z,LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))-1)*LEN(B1)+1,LEN(B1))),
TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",LEN(B1))),(ROW($Z1:INDEX($Z:$Z,LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))-1)*LEN(B1)+1,LEN(B1))),
0)
<ROW($Z1:INDEX($Z:$Z,LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1)),"",
TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",LEN(B1))),(ROW($Z1:INDEX($Z:$Z,LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))-1)*LEN(B1)+1,LEN(B1)))))

           
           
           =substitute(C1,",","&",len(C1)-len(substitute(C1,",","")))

           
           
           
            
             
            
           
           
           
           
           
           
            
             
            
           
           
           
           
            
            
            
             轮班