excel：根据3列检查重复的行，并保留一行 [英] excel: check for duplicate rows based on 3 columns and keep one row

问题描述

这可能是一个很多问题，但我想知道如果以下是可能的。这一切都是基于我在复制范围和粘贴值在另一张表的特定范围

我有一张表格，其中包含大约12列，相同类型的数据被复制到这意味着两行或更多行可能包含完全相同的数据，但是如果我可以检查重复项，则将基于3个特定列。

我不想为此任务使用自动过滤器或条件格式化，因为工作表可以包含数百到数千行，并且我想要能够找到重复项，删除它们并保留一个原件。我也读过其他帖子，在这里关于删除重复基于列条件，但到目前为止没有一个为我工作。

数据范围是从A到P和下并且标识重复的3列是A（数字，ID），B（它是一个日期）和P（它是一个文本字符串）。如果这些数据来自，它将始终具有相同的格式，并且将始终置于相同的列中，这就是为什么如果A，B，P相同，那么它被认为是重复的。

感谢您的帮助。

解决方案

此代码适用于我（ lastrow 根据此asnwer by @SiddharthRout）：

 子测试（）
 
 Dim lastrow As Long 
 
使用ThisWorkbook.Worksheets（Sheet1）
如果Application.WorksheetFunction.CountA（.Cells）<> 0然后
 lastrow = .Cells.Find（what：=*，_ 
 After：=。Range（A1），_ 
 Lookat：= xlPart，_ 
 LookIn：= xlFormulas，_ 
 SearchOrder：= xlByRows，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）.Row 
 Else 
 lastrow = 1 
 End If 
 
'Array（1，2，16）表示1  - 对于A，2表示B，16表示P列
 .Range（A1：P& ; lastrow）.RemoveDuplicates列：= Array（1，2，16），_ 
标题：= xlYes 
结束
 End Sub 
  / pre> 
This might be a bit much to ask but I'm wondering if the following is possible. this is all based on my initial question on Copy Range and Paste Values in another Sheet's specific Range

I have a sheet that contains approximately 12 columns where the same type of data is copied to which means that two or more rows could contains the exact same data, however if I could check for duplicates it would be based on 3 specific columns.

I don't want to use AutoFilter or Conditional Formatting for this task because the sheet can contain hundreds to thousands of rows and I want to be able to locate duplicates, delete them and keep just one original. I have also read other posts on here about Removing Duplicates based on column conditions but so far none has worked for me.

The Range of data is from A thru P and down and the 3 columns that would identify a duplicate are A (which is numeric, an ID), B (which is a date), and P (which is a Text string). Where this data comes from, it will always have the same format and will always be placed in the same columns which is why if A,B,P are the same, then it is considered a duplicate.

Thanks for the help in advance.
解决方案
This code works for me (lastrow based on this asnwer by @SiddharthRout):
Sub test()

    Dim lastrow As Long

    With ThisWorkbook.Worksheets("Sheet1")
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastrow = 1
        End If

        'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns
        .Range("A1:P" & lastrow).RemoveDuplicates Columns:=Array(1, 2, 16), _
            Header:=xlYes
    End With
End Sub


                        
这篇关于excel：根据3列检查重复的行，并保留一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！