Problems removing duplicates from a LONG list based on conditions

[metalpoker]

Hi,

I recently posted this question on here and received some help from a member, but we encountered a problem that could not be fixed and thus I need to ask for help again!! My knowledge of VBA is limited, and whereas I have understood what I have learnt so far I am not experienced enough to be writing code this long myself so if any experts out there can be help me I will be eternally grateful.

I have lists of people who have entered competitions which go through a data cleaning process and end up in the format of the file attached to this post. That part works fine, but what I need to do next is run a new macro to remove the duplicated entries so that where people have entered the competition more than once my final list should exclude the duplicates and display only 1 row per unique user.

I need unique users to be determined by a matching name AND email address (columns A and B). The address, phone and postcode columns can be ignored but do need to be displayed in the final list which should retain the original column structure.

This is the hard bit...

If someone is logged into the website when entering a competition the status of "Yes" is automatically assigned to the VIP column (G), but then some people will enter the competition repeatedly and if they are not logged in on any of these occasions a "No" for VIP is returned. My problem is that in the final non-duplicated list I need to know who is a VIP, and therefore if any of one persons has a Yes in that column, even if it is just one out of several entries I need the final list to retain that yes for their VIP status.

This was my original code that worked perfectly for removing duplicates but did not take into account the Yes/No condition:

Sub RemoveDuplicateRecords()
     '   Local Variables
    Dim rngData         As Range, cell  As Range
     '   Set the data range ( based on "Email Address" field )
    Set rngData = Worksheets("test").Range("B2", Worksheets("test").Range("B2").End(xlDown)).Offset(0, -1)
     '   Sort the table by Name / Email Address
    rngData.EntireRow.Sort Key1:=rngData.Range("A2").Offset(0, 1), Order1:=xlAscending, Key2:=rngData.Range("A2").Offset(0, 2), Order2:=xlAscending, Key3:=rngData.Range("A2").Offset(0, 0), Order3:=xlAscending
     '   Remove duplicate entries ( Name / Email Address determine duplicate entries )
     ' For speed purposes use clearcontents and then resort list
    For Each cell In rngData
        If cell.Offset(0, 1) = cell.Offset(1, 1) And cell.Offset(0, 2) = cell.Offset(1, 2) Then
            If cell.Offset(1, 0) = "" And cell.Offset(0, 0) <> "" Then cell.Offset(1, 0) = cell.Offset(0, 0)
            cell.EntireRow.ClearContents
        End If
    Next cell
     '   Sort the table by Email Address and then Name
    rngData.EntireRow.Sort Key1:=rngData.Range("A2").Offset(0, 0), Order1:=xlAscending, Key2:=rngData.Range("A2").Offset(0, 1), Order2:=xlAscending
End Sub

And this is what another user wrote that seemed to work but when I tested it thoroughly there was a lot of "No"s returned for users who should have been "Yes":

Sub remove_dups()

Dim lastrow As Long
Dim i As Long, j As Long

    Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
   
lastrow = Range("A1").End(xlDown).Row

For i = 2 To lastrow

    If Range("A" & i).Value = Range("A" & i + 1).Value Then
    
        If Range("B" & i).Value = Range("B" & i + 1).Value Then
        
            If Range("G" & i).Value = "Yes" Then
                Rows(i + 1 & ":" & i + 1).Delete
            ElseIf Range("G" & i).Value = "No" Then
                Rows(i & ":" & i).Delete
            End If
            
            lastrow = lastrow - 1
            
        End If
    
    End If
                
Next

End Sub

--------------

In the example file "Catherine Paice" "Francis Healey" and "Wendy Price" should all be returned in the final non-duplicated list as YES because they have yes and no from various entries, but Julia Cook should have her final listing as NO as she was no every time.

I really hope this makes sense!!! The real databases are much much bigger 3k - 5k rows before duplicates removed, hence this being way beyond my knowledge...

Any help would be GREATLY appreciated - as time is running out for me to get this all sorted, and once this is done I have something even bigger to tackle!!


Susie