EXCEL中同一单元格里面，有很多相同的数据，提取相同的数只要一个不相同也要提取出来····有没有公式？

Question

如题所述

Answer 1

前一段时间写了个代码，你这个应该也可以用
Public Function PS(MM As String) As String '对单元格内容进行排序
Dim Ar1() As String, s As String, M1 As String, Ar As Variant
Dim i%, j%, n%, k%, p%, ss%
With Application.WorksheetFunction
M1 = UCase(.Substitute(.Substitute(.Substitute(MM, "，", ","), "。", ","), ".", ","))
End With
Ar1 = Split(M1, ",") '分离
n = UBound(Ar1)
'排序
For i = 0 To n - 1
For j = 0 To n - 1 - i
If Left(Ar1(j), 1) <> Left(Ar1(j + 1), 1) Then
If Ar1(j) > Ar1(j + 1) Then
temp = Ar1(j)
Ar1(j) = Ar1(j + 1)
Ar1(j + 1) = temp
End If
Else
If Len(Ar1(j)) <> Len(Ar1(j + 1)) Then
If Len(Ar1(j)) > Len(Ar1(j + 1)) Then
temp = Ar1(j)
Ar1(j) = Ar1(j + 1)
Ar1(j + 1) = temp
End If
Else
If Ar1(j) > Ar1(j + 1) Then
temp = Ar1(j)
Ar1(j) = Ar1(j + 1)
Ar1(j + 1) = temp
End If
End If
End If
Next j
Next i
'查重复项
ss = 0
For k = 0 To n - 1
For p = 1 + k To n
If Ar1(k) = Ar1(p) Then
s = s & "," & Ar1(p)
Ar1(k) = "aa"
ss = ss + 1
GoTo nk
End If
Next p
nk:
Next k

If s <> "" Then
MsgBox ("共有" & n + 1 & "个；" & Chr(13) & "其中有重复项" & ss & "个，" & Chr(13) & "为" & s & Chr(13) & "实际为" & n + 1 - ss & "个")
End If
'去除空白
For o = 0 To n
If Ar1(o) = "" Then Ar1(o) = "aa"
Next o
'去除重复项
Ar = VBA.Filter(Ar1, "aa", False)
'组合
PS = Join(Ar, ",")
End Function

有问题再HI我追问

我要的结果是
B1 1,2,3
B2 2,4
B3 A09X01
B4 B08Y09，C07Z04
B5 5,7

追答

在B1写入公式 =ps(a1) ,然后往下拉

Answer 2

=IF(--LEFT(A2),"",0)&SUM(RIGHT(LARGE(IF(ISERR(FIND({0;1;2;3;4;5;6;7;8;9},A2)),,FIND({0;1;2;3;4;5;6;7;8;9},A2)*10+{0;1;2;3;4;5;6;7;8;9}),{1;2;3;4;5;6;7;8;9;10}))*10^{1;2;3;4;5;6;7;8;9;10})/10

数组公式追问

A1列数据1,1,1,1,1,2,2,2,2,3,3,3
2,2,2,4,4,4,4
A09X01,A09X01,A09X01
B08Y09,B08Y09,C07Z04,C07Z04
5,5,5,5,7,7,7
B1列结果
123
24
#VALUE!
#VALUE!
57
······好还是不行

追答

数据之间有符号？
=IF(--LEFT(SUBSTITUTE(A1,",",)),"",0)&SUM(RIGHT(LARGE(IF(ISERR(FIND({0;1;2;3;4;5;6;7;8;9},SUBSTITUTE(A1,",",))),,FIND({0;1;2;3;4;5;6;7;8;9},SUBSTITUTE(A1,",",))*10+{0;1;2;3;4;5;6;7;8;9}),{1;2;3;4;5;6;7;8;9;10}))*10^{1;2;3;4;5;6;7;8;9;10})/10

追问

1,1,1,1,1,1,1,1 1
2,,2,2,2,3,3,3,3 23
4,4,4,4,4,4,4 4
5,5,5,5,5,5,5 5
6,6,6,6,6,6 6
7,7,7,7,7,7 7
6,6,6,5,5,5,5 65
1,1,1,2,2,2,3,3 123
A23X24,A23X24,A23X24 #VALUE!
A14Z12,A14Z12,A14Z12 #VALUE!
E16Z12,E16Z12,E16Z12 #VALUE!
A11Y18,A11Y18,A11Y18 #VALUE!
还是不行哦···

追答

数字的可以
混合有文本的不行

追问

我可以把表格里面的数据···分裂开···然后应该还有公式吧？呵呵

追答

在1列可以