wps中两列数据找不同并删除
A:1.2.3.4.5.6
B:!.@.#.$.%.^
C:4.5.6.1.2.3.7.8
D:$.%.^.!.@.#.&.*
B对应A列,D列对应C列,怎么把C列中7.8和对应的&*找出并删除,然后将C列按A列排序(D必须和C列对应)
Sub shanchu()
'本代码适用于:数字不超过10,数字一一对应的都是单个字符
Dim n%, k%, m%, x%, y%, p%, q%
Dim A(), B(), C(), D(), CH(), DH()
n = 1
Do
If Len(Cells(n, 1)) <> Len(Cells(n, 2)) Or _
Len(Cells(n, 3)) <> Len(Cells(n, 4)) Then
MsgBox "A" & n & "与B" & n & " 或 C" & n & "与D" & n _
& "字符不是单个对应,不能删除。" & Chr(10) & "请单击“确定”进行下一行。"
GoTo 100
End If
k = (Len(Cells(n, 1)) + 1) / 2
m = (Len(Cells(n, 3)) + 1) / 2
ReDim A(1 To k)
ReDim B(1 To k)
ReDim C(1 To m)
ReDim D(1 To m)
ReDim CH(1 To k)
ReDim DH(1 To k)
x = 0: y = 0: p = 0: q = 0
For i = 1 To 2 * k - 1 Step 2
x = x + 1
A(x) = Mid(Cells(n, 1), i, 1)
B(x) = Mid(Cells(n, 2), i, 1)
Next i
For i = 1 To 2 * m - 1 Step 2
y = y + 1
C(y) = Mid(Cells(n, 3), i, 1)
D(y) = Mid(Cells(n, 4), i, 1)
Next i
For i = 1 To m
For j = 1 To k
If C(i) = A(j) Then
p = p + 1
CH(p) = C(i)
DH(p) = D(i)
Exit For
End If
Next j
Next i
Cells(n, 3) = Join(CH, ".")
Cells(n, 4) = Join(DH, ".")
100
n = n + 1
Loop Until Cells(n, 1) = ""
End Sub
利用辅助列,比如E列;
E1:
=VLOOKUP($A1,$C$1:$D:$100,2,0)
下拉复制到所有A列有数据的行数。
这样,A、E两列即为排除了与A列不相同的行的返回结果;
如果确要返回C、D列显示,可分别复制A、E列,到C、D列以"选择性粘贴"-》"数值"的方式贴回即可。
用公式match,如图,只要能出来数字,就代码在另一列中有对应的重复项目。