Excel unique data rows [Solved/Closed]

Report
-
 quiz -
Hello,

I need to compare two columns of data for uniqueness. Column a has a list of names and column b has a list of date/times. I need to be able to delete the rows where the data for duplicates show a date/time older than the other (duplicate name or names). Any help in appreciated.

Thanks

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
764
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
764
So you have only two columns in your actual workbook ? Could you post a workbook instead of typing here as it is hard to see how the data is laid out here.
Ok, I've never done this before but I think I did it right. lol Thanks for any help you can provide.

http://www.editgrid.com/user/care/quiz2
Hi rizvisa1,
Here's the link to the workbook.
http://www.editgrid.com/user/care/quiz2
Thanks.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
764
Two questions
1. Can data be sorted.
2. Can rows be delete

and bonus question
3 Would a macro work for you
Yes, the data can be sorted and deleted. I've done conditional formatting, filtering and sorting but that only gets me so far, and then I still have to sift through. A macro would be great! Thanks.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
764
Try this

Sub KeepLatestData()
Dim lMaxRows As Long

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("A2:D" & lMaxRows).Sort _
                    Key1:=Range("A3"), Order1:=xlAscending, _
                    Key2:=Range("B3"), Order2:=xlDescending, _
                    Header:=xlYes
    
    Do While lMaxRows > 2
    
        If (Cells(lMaxRows, "A") = Cells(lMaxRows - 1, "A")) Then
        
            If (Format(CDate(Cells(lMaxRows, "B")), "YYYYMMDD") = Format(CDate(Cells(lMaxRows - 1, "B")), "YYYYMMDD")) Then
            
                If (CDate(Cells(lMaxRows, "B")) < CDate(Cells(lMaxRows - 1, "B"))) Then
                    Rows(lMaxRows).Delete
                End If
            
            End If
        
        End If
        
        lMaxRows = lMaxRows - 1
    
    Loop
End Sub

Your macro worked perfectly! Thanks so much, you made my day and my life a bit easier!!!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!