CODEWITHSUNDEEP
excelpowerbi
Craig
Craig

Reputation: 1940

How can I remove duplicate rows (in Excel) based on another column's values?

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.

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?

Upvotes: 1

Views: 623

Answers (1)

basodre
basodre

Reputation: 5770

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.

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

Upvotes: 1

Related Questions