CODEWITHSUNDEEP
excelexcel-formula
acaselli
acaselli

Reputation: 27

Remove duplicates in one column based on a date in another column

I have an excel spreadsheet with two columns: Column A contains a list of names and Column B contains dates.

I want to get rid of duplicate entries based on dates. This is an example

A     B

John  01/03/2020

John  01/03/2020 

Bob   01/03/2020

John  02/03/2020

Bob   02/03/2020

Bob   02/03/2020

I want to remove duplicates that have the same date, so the final result should be:

A     B

John  01/03/2020

Bob   01/03/2020

John  02/03/2020

Bob   02/03/2020

Upvotes: 0

Views: 536

Answers (2)

basic
basic

Reputation: 11968

Why aren't you using the standard Remove duplicates functionality?

enter image description here

Upvotes: 2

Error 1004
Error 1004

Reputation: 8230

If you decide to use VBA try:

Sub test()

    Dim i As Long, y As Long, Lastrow As Long
    Dim strName As String
    Dim dtDate As Date

    With ThisWorkbook.Worksheets("Sheet1")
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = Lastrow To 1 Step -1

            For y = i - 1 To 1 Step -1
                If (.Range("A" & i).Value = .Range("A" & y).Value) And (.Range("B" & i).Value = .Range("B" & y).Value) Then
                    .Rows(i).EntireRow.Delete
                End If

            Next y

        Next i

    End With

End Sub

Upvotes: 0

Related Questions