赵婧瑄 发表于 2021-11-28 09:02:00

同一个格内重复的数字只保留一个

要求在表里边，先谢

环成 发表于 2021-12-3 03:26:15

如果只有一个重复
=REPLACE(A1,MIN(IF(ROUNDUP(FIND(MID(A1,{1,2,3,4,5}*4-3,3),A1)/4,)={1,2,3,4,5},9,{1,2,3,4,5}))*4-3,4,"")

多个重复
=TEXTJOIN(" ",,TEXT(FILTERXML("<a><b>"&SUBSTITUTE(A1," ","</b><b>")&"</b></a>","a/b"),"000"))

365
=TEXTJOIN(" ",,UNIQUE(MID(A1,{1;2;3;4;5}*4-3,3)))

搜索 发表于 2021-12-29 18:58:07

=TEXTJOIN(" ",,UNIQUE(TRIM(MID(A1,SORT(UNIQUE(IFERROR(FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))+1,4))))

骑马过海 发表于 2022-2-10 14:12:14

低版本，多重复的
=SUBSTITUTE(TEXT(SUM(LARGE(IF(MMULT(N(--MID(A1,{1,2,3,4,5}*4-3,3)=ROW($1:$99)),{1;1;1;1;1}),ROW($1:$99),),ROW($1:$5))*1000^(ROW($1:$5)-1)),REPT("000 ",5)),"000 ",)

陈保安 发表于 2022-2-25 02:47:26

拆分、去重、再合并：
=TEXTJOIN(" ",,UNIQUE(SSPLIT(A1)))

Public Function SSPLIT(ByVal strText$, Optional ByVal Delimiter = " ", Optional ByVal Limit& = -1, Optional ByVal Compare As VBA.VbCompareMethod = vbBinaryCompare)
SSPLIT = VBA.Split(strText, Delimiter, Limit, Compare)
End Function
Public Function TEXTJOIN(ByVal Delimiter, ByVal ignore_empty, ParamArray text()) As String
Dim tmptext As Variant, rng As Variant, cel As Variant, i As Long
If IsMissing(Delimiter) Then Delimiter = "" Else Delimiter = CStr(Delimiter)
If IsMissing(ignore_empty) Then ignore_empty = True Else ignore_empty = CBool(ignore_empty)
tmptext = ""
For i = 0 To UBound(text)
rng = text(i)
If IsArray(rng) Then
    For Each cel In rng
      If Len(cel) Or Not ignore_empty Then tmptext = tmptext & Delimiter & cel
    Next cel
Else
    If Len(rng) Or Not ignore_empty Then tmptext = tmptext & Delimiter & rng
End If
Next
TEXTJOIN = Mid(tmptext, Len(Delimiter) + 1)
End Function
Public Function UNIQUE(ParamArray rng())
Dim dic As Object, itm, ocel, skey
Set dic = CreateObject("scripting.dictionary")
For Each itm In rng
If TypeName(itm) = "Range" Then
    For Each ocel In itm
      dic(ocel.Value) = ""
    Next
Else
    If IsArray(itm) Then
      For Each skey In itm
      dic(skey) = ""
      Next
    Else
      dic(itm) = ""
    End If
End If
Next
UNIQUE = dic.keys
End Function

半路人生 发表于 2022-2-27 19:18:18

=CONCAT(IF(FIND(MID(A1,{1;5;9;13;17},3),A1)={1;5;9;13;17},MID(A1,{1;5;9;13;17},4),""))

张福 发表于 2022-3-1 07:44:07

会点PQ的就很容易看懂按分隔符拆分列 ，
已添加索引 ，添加个索引，也就是相当于序号表示第几行数据
逆透视的其他列 ，并且把透视结果转换文本格式（不然文本008会被自动视为常规8）
分组的行 ，按照索引号分组，对文本数字求和，把编辑栏的LIST SUM字样改成Text.Combine,并且后面以双引号和链接符号添加([值]," "
)，这步骤也叫分类聚合，具体建议百度一下PQ Text.Combine字样
页: [1]
查看完整版本: 同一个格内重复的数字只保留一个