更新时间:2023-02-13 18:05:49
这样做的一个方法是在原始数据旁边添加一个第二列,其中填写下列公式: / p>
= IF(COUNTIF($ A $ 1:$ A $ 5000,A1)> 1,A1&(& (A $ 1:A1,A1)&),A1)
在A1:A5000。请注意, COUNTIF
非常低效,所以如果你有大量的数据,这可能需要一段时间来计算和影响你的工作簿性能。
对于大型工作簿,我会考虑使用VBA Worksheet_Change事件来编辑值。该代码应插入相应的Worksheet模块中。在5000个测试记录上,它有几秒钟的滞后。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRng As Range
Dim dataArr()As Variant,output()As String
Dim y As Long,i As Long,j As Long,tmpcount As Long
'将A1更改为要索引的列的起始地址。
设置dataRng =范围(A1)。调整大小(Me.UsedRange.Rows.Count,1)
如果不相交(目标,数据Rng)没有,则
dataArr = dataRng.Value
ReDim输出(1到UBound(dataArr,1),1到1)
'从数组中的数据中删除旧的计数。
对于y = 1对于UBound(dataArr,1)
如果右(dataArr(y,1),1)=)然后
dataArr(y,1)= Left(dataArr (y,1),InStr(dataArr(y,1),() - 1)
结束如果
下一个y
对于i = 1 To UBound(dataArr ,1)
tmpcount = 0
输出(i,1)= dataArr(i,1)
对于j = 1到UBound(dataArr,1)
如果dataArr ,1)= dataArr(j,1)Then
tmpcount = tmpcount + 1
如果j = i和tmpcount> 1然后
输出(i,1)= dataArr(i,1 )$
退出
结束If
如果j> i和tmpcount> 1然后
输出(i,1 )= dataArr(i,1)&(& tmpcount - 1&)
退出
结束If
结束If
下一步j
Next i
调用打印输出(output,dataRng)
结束如果
结束Sub
私有子打印输出(什么As Variant,其中As范围)
Application.EnableEvents = False
where.Value = what
Application.EnableEvents = True
End Sub
小心我做了几个很大的假设:
It is very easy to format the cells that have duplicated values (like setting specific background on them or something other style) using the "Conditional formatting", but how can I change their text?
For example:
A1 2332
A2 2333
A3 2334
A4 2334
to become:
A1 2332
A2 2333
A3 2334(1)
A4 2334(2)
One way to do this is to just add a second column next to your original data with the following formula filled down:
=IF(COUNTIF($A$1:$A$5000,A1)>1,A1& " (" & COUNTIF(A$1:A1,A1) & ")",A1)
where your original data is in A1:A5000. Beware that COUNTIF
is pretty inefficient, so if you have a lot of data, this could take a while to calculate and affect your workbook performance.
For a large workbook, I'd consider using a VBA Worksheet_Change event to edit the values in place. This code should be inserted in the appropriate Worksheet module. On 5000 test records it had a couple second lag.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dataRng As Range
Dim dataArr() As Variant, output() As String
Dim y As Long, i As Long, j As Long, tmpcount As Long
'Change "A1" to address of start of column you want to index.
Set dataRng = Range("A1").Resize(Me.UsedRange.Rows.Count, 1)
If Not Intersect(Target, dataRng) Is Nothing Then
dataArr = dataRng.Value
ReDim output(1 To UBound(dataArr, 1), 1 To 1)
'Strip old counts from data once in array.
For y = 1 To UBound(dataArr, 1)
If Right(dataArr(y, 1), 1) = ")" Then
dataArr(y, 1) = Left(dataArr(y, 1), InStr(dataArr(y, 1), " (") - 1)
End If
Next y
For i = 1 To UBound(dataArr, 1)
tmpcount = 0
output(i, 1) = dataArr(i, 1)
For j = 1 To UBound(dataArr, 1)
If dataArr(i, 1) = dataArr(j, 1) Then
tmpcount = tmpcount + 1
If j = i And tmpcount > 1 Then
output(i, 1) = dataArr(i, 1) & " (" & tmpcount & ")"
Exit For
End If
If j > i And tmpcount > 1 Then
output(i, 1) = dataArr(i, 1) & " (" & tmpcount - 1 & ")"
Exit For
End If
End If
Next j
Next i
Call printoutput(output, dataRng)
End If
End Sub
Private Sub printoutput(what As Variant, where As Range)
Application.EnableEvents = False
where.Value = what
Application.EnableEvents = True
End Sub
Beware I've made a couple of big assumptions: