如何根据另一个列的值删除重复行(在Excel中)?

Context: I have a Excel table with N rows and 3 columns: SerialNum, DeviceID, and DateTime. DeviceID is unique (as in no values will repeat). However, there can be a 1:N relationship between SerialNum and DeviceID (so the same serial number may have multiple DeviceIDs). I want to remove the duplicate SerialNum values so each SerialNum has a single associated DeviceID.

上下文:我有一个有N行和3列的Excel表:SerialNum、DeviceID和DateTime。DeviceID是唯一的(因为没有重复的值)。然而，在SerialNum和DeviceID之间可能存在1:N的关系(因此相同的序列号可能有多个DeviceIDs)。我想删除重复的SerialNum值，这样每个SerialNum都有一个关联的DeviceID。

Question: The trouble is that for each group of duplicate serial numbers, I want to keep the DeviceID whose DateTime value is the latest (in other words, most current). Is there a way to do this?

问题:问题是，对于每组重复的序列号，我都希望保留DeviceID，它的DateTime值是最新的(换句话说，是最新的)。有办法吗?

1 个解决方案

#1

One option is to test each item in Column A, Serial Numbers, to determine the highest Date/Time for that serial number. The only real risk I see in using this method if two entries for a serial number have the same exact date/time. If you think this is a likelihood, you can test for it, or possibly run a second loop to delete duplicate SNs based on a rule you're comfortable with.

一个选项是测试列A中的每个项目，序列号，以确定该序列号的最高日期/时间。如果一个序列号的两个条目具有相同的日期/时间，那么我在使用该方法时所看到的唯一真正的风险是。如果您认为这是一种可能性，那么您可以对它进行测试，或者根据您熟悉的规则运行第二个循环来删除重复的SNs。

See the below code and let me know if it meets your needs.

请参阅下面的代码，并让我知道它是否满足您的需要。

Sub RemoveDuplicateSerialNumbersUnlessSerialNumberIsMostRecentDateTimeValue_Dawg()
    Dim nLastRow As Long
    Dim dtMaxDateTime As Date
    Dim strFormula As String

    nLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    For i = nLastRow To 2 Step -1
        strFormula = "=MAX(IF(A2:A16=" & Cells(i, 1).Address & ",C2:C16))"
        dtMaxDateTime = Evaluate(strFormula)

        If Cells(i, 3).Value = dtMaxDateTime Then
            'Do Nothing Unless You Want TO
        Else
            Rows(i).Delete
        End If
    Next i

End Sub