Excel保留前三行并删除其余的重复项 [英] Excel keep 1st three rows and delete rest of duplicates

问题描述

我有20K数据表，包含第1列的多个重复。我需要为每个数字保留第1列的第1个三个重复项，并删除其余部分。

I have 20K data sheet that contain multiple duplicates for column 1. I need to keep 1st three duplicates for column 1 for each number and delete rest of it.

我需要保持黄色突出显示，删除其余部分。

I need to keep the yellow highlighted and delete the rest of it.

推荐答案

所以，去VBA路线，你需要循环选择标签中的每一行并测试该值以查看它是否重复，如果它是重复的，那么您将增加一个计数器变量，一旦该计数器变量命中3，您开始删除行。

So, going the VBA route, you'll need to loop through each row in your tab and test the value to see if it's a duplicate, if it's a duplicate then you'll increase a counter variable and once that counter variable hits 3 you start deleting rows.

如果您没有使用VBA，这有点复杂。请花点时间玩代码并了解它。我已经写了一些意见来帮助你。

This is a little complicated if you haven't worked with VBA. Please take some time to play with the code and understand it. I've written comments in it to help out.

Sub keepFirstThreeDuplicates()
    Dim workingRow As Integer
    Dim currentDup As String
    Dim dupCounter As Integer
    Dim wsheet As Worksheet

    'change this to your tab name
    Set wsheet = ThisWorkbook.Sheets("Sheet1")

    'loop through every row just guessing that your data starts at row 1 (A1) and goes to 50000 (A50000)
    For workingRow = 1 To 50000

        If workingRow = 1 Then 'we are at the first row, so grab the value and set dupCounter to 1
            currentDup = wsheet.Cells(workingRow, 1).Value 'Assuming column 1, so this is Cell A1
            dupCounter = 1
        ElseIf currentDup = wsheet.Cells(workingRow, 1).Value Then 'we have another duplicate
            If dupCounter = 3 Then 'We already have three duplicates, so delete the row, and set the row back one (because we deleted the row)
                wsheet.Rows(workingRow).Delete
                workingRow = workingRow - 1
            Else
                dupCounter = dupCounter + 1
            End If
        Else 'We are at a new value, so grab the value and set dupCounter to 1
            currentDup = wsheet.Cells(workingRow, 1).Value
            dupCounter = 1
        End If

        'exit the for loop if we hit a blank
        If currentDup = "" Then Exit For
    Next workingRow

End Sub

如果您是VBA的新功能，请使用以下代码：

If you are super new to VBA, to use this code:

1. 在您的工作簿中，按Ctrl + F11键到Visual Basic
   编辑器（VBE）

1. While in your workbook, hit Ctrl+F11 to get to the Visual Basic Editor (VBE)

您的工作簿将被称为VBAProject 在VBAProject面板中。
右键单击并选择插入>>模块

Your workbook will be called a "VBAProject" in the VBAProject panel. Right click on it and select Insert>>Module

双击新模块Module1打开它。

Double click your new module "Module1" to open it.

粘贴此代码。

要运行它，请点击代码中的某个地方并按下
顶部的播放按钮（或键盘上的F5）。确保您编辑代码以适应
您的工作簿的需要，例如将选项卡名称更改为您的选项卡。
还要确保在运行之前备份您的工作簿，因为
将删除行，您将无法撤消。

To run it, click somewhere in the code and hit the play button up top (or F5 on your keyboard). Make sure you edit the code to suit the needs of your workbook like changing the tab name to your tab. Also make sure to back up your workbook before running it as this will delete rows and you won't be able to undo.

最后，这只是一种方法。这并不意味着100％满足您的需求，因为我猜测您的数据是哪一列，数据已经由您的重复列排序，而其他什么也没有。这只是指出你在VBA的坚实方向。它在我的测试工作簿上工作，在一个新的工作簿的A列中创建了一个假列表。

Lastly, this is just one way to do it. It's not meant to 100% address your needs since I'm guessing at number of rows, which column your data is in, that the data is already sorted by your duplicate column, and other whatnot. This is just to point you in a solid direction in VBA. It worked on my test workbook for a fake list I created in Column A of a fresh workbook.

这篇关于Excel保留前三行并删除其余的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！