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:
' Local Variables
Dim rngData As Range, cell As Range
' Set the data range ( based on "Email Address" field )
Set rngData = Worksheets("test").Range("B2",
' 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),
' 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) =
' 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
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":
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 _
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
lastrow = lastrow - 1
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!!