Excel unique data rows

Solved/Closed
quiz - Jun 22, 2010 at 12:34 PM
 quiz - Jun 29, 2010 at 09:28 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 22, 2010 at 06:59 PM
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
0
name date/time
alice cooper 5/12/10 6:15 AM
howdy doody 7/14/09 9:00 AM*
howdy doody 7/14/09 9:07 AM
jane doe 6/30/09 12:00 AM*
jane doe 6/30/09 12:45 AM
jane doe 4/15/10 11:00 AM
john doe 4/10/10 12:31 AM
kevin brown 1/21/10 4:30 PM*
kevin brown 1/21/10 4:35 PM*
kevin brown 1/21/10 5:00 PM
mark doe 3/31/10 9:00 AM
sally doe 4/10/10 2:00 PM
sally fields 6/13/10 10:00 AM



What I'm trying to do is remove instances of duplicate records that occurr on the same day, where for example, the name and date are the same but keeping the instance where the time is the most recent. See asterix (these are the ones I would want to be removed)
I know this can be done somewhat through conditional formating and a sort/filter, but still some sifting though will still have to be done. Hoping that maybe it could be automated somehow to make for a quicker process. I have a large list of records (100,000+).
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 24, 2010 at 06:39 PM
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.
0
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
0
Hi rizvisa1,
Here's the link to the workbook.
http://www.editgrid.com/user/care/quiz2
Thanks.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 25, 2010 at 03:37 PM
Two questions
1. Can data be sorted.
2. Can rows be delete

and bonus question
3 Would a macro work for you
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 28, 2010 at 05:50 PM
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

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