如何在Excel中删除副本?

I have a column with 3 of the same keywords taking up 3 rows. I need to clear the values in the first two rows in each instance.

我有一列有3个相同的关键字，共3行。我需要在每个实例中清除前两行中的值。

I have highlighted the rows as an example in the screenshot:

我在截图中突出显示了行:

Thanks for your time and help!

谢谢您的时间和帮助!

4 个解决方案

#1

If you want to replace the duplicate keywords with blanks, you can make a new column to the right of your Keyword column and use an IF-function =IF(B2=B1,"",B2). If you copy that down, your original Keyword column will be copied except the duplicates will be blank.

如果想用空格替换重复的关键字，可以在关键字列的右边新建一个列，并使用If -function = If (B2=B1，""，B2)。如果您将它复制下来，那么您的原始关键字列将被复制，除非副本将为空。

#2

You might want to try the below:

你可以试试下面的方法:

On the Home Tab, Click

在Home选项卡上，单击

Conditional Formatting under Styles Group
样式组下的条件格式。
Highlight Cell Rules
突出细胞规则
Duplicate Values
重复的值

This will highlight all the duplicates in Pink(Default Color). You can then Filter by Color. Copy the unduplicated data and paste into another column.

这将突出显示所有复制的粉红色(默认颜色)。然后你可以根据颜色进行过滤。复制未复制的数据并粘贴到另一列。

#3

Use Remove Duplicates excel built in command. Under Data tab you will find remove duplicates command.

使用删除复制的excel内置命令。在Data选项卡下，您将找到删除重复命令。

(1) First select desired column of range.
(2) Hit on Remove Duplicates.
(3) Follow the on screen instructions and do as necessary.

#4

Here is vba Function that will remove duplicates and does not shift the value up

这里是vba函数，它将删除重复的内容，并且不会将值向上移动

Option Explicit
Sub ClearDuplicates()

    Dim i As Long
    Dim lRng As Long

    lRng = Range("A65536").End(xlUp).Row

    For i = lRng To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" & i), _
            Range("A" & i).Value) > 1 Then
            Range("A" & i).ClearContents
        End If
    Next i

End Sub

Example

例子

#1

#2