Eliminating Duplicate records

[Andrew Wilson]

Hello,
firstly let me say i am a complete newbie to posting in forums although i have used them often to point me in the right direction.

I Have an excel 2007 table that gets updated every time a user adds or amends a record in a database even if they only press save. i need to count the entries by each user to compare to another figure but to do this reliably i need to exclude those records that don't really contain any useful data i.e. user pressed save more that once for some reason.

i have columns for User, Update Date-Time, Enquiry Id, among others

so the logic would be; remove rows where user ID and Enquiry Id are the same and the difference in Update Date-Time is less than 5 minutes.

i have some knowledge of VBA although would prefer a solution that does not use it as this needs to be used by non VBA trained Staff.

any help would be great
thanks in advance

 

[patel45]

attach a link to a sample file with comments

 

[Andrew Wilson]

thanks for taking the time patel45

https://docs.google.com/open?id=0B0Ojh1f2IgNfNlItX2ZDZTZ6UGM

hope this helps

 

[patel45]

do you want identify those rows and remove them ? or only one ? or hilight them ?

 

[Andrew Wilson]

i would like to identify (Highlight) those rows for review, and i will ultimately be removing all but the first (as in happened first) row.
so i suppose if i use a new column to contain an indicator for Duplicate and that indicator includes a count of how many times that row is duplicated then i could just keep all the rows where the duplicate count is 1
the bit i am struggling to achieve is the "range of time" comparison i have tried Index and match and very long "if" statements but nothing really satisfactory
thanks again

 

[hurgadion]

Hi,
let Your Data be in Columns B:D. Try using a following Code:

Sub NoDup()
Dim a&, i&, k&, tbl, tblk(), w As String, x&
Dim Nodupes As New Collection

a = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To a
  tbl = Application.Transpose(Application.Transpose(Range("B" & i & ":D" & i).Value))
  w = Join(tbl, "_")
  On Error Resume Next
  Nodupes.Add w, CStr(w)
  If Err.Number = 0 Then
    x = x + 1
    ReDim Preserve tblk(1 To 3, 1 To x)
    For k = 1 To 3
      tblk(k, x) = tbl(k)
    Next k
  End If
Next i
Range("B2:D" & a).ClearContents
Cells(2, 2).Resize(x, 3) = Application.Transpose(tblk)
End Sub

Best regards.

 

[Andrew Wilson]

thanks Hurgadion,

it seems to work, although it removes them before i can check, but most importantly i would love a brief explanation of what it is doing along the way, especially as i need to expand this to 12 columns.

 

[Andrew Wilson]

thanks,
firstly, it does seem to work, but sorry to be a pain could you give a brief explanation of what is going on here, i need to expand the columns to 12 and how can i control what the tolerance (In Time) is before a duplicate is created, i just can not see where that is set,

 

[hurgadion]

1. I assumed, that Your Data there is in Columns B:D

2. In the Code:

a = Cells(Rows.Count, 2).End(xlUp).Row

I found the number of the last, nonempty Row in Column B (because I wrote 2)

3. In the Loop I analyse step by step records in Columns B:D, see:

tbl = Application.Transpose(Application.Transpose(Range("B" & i & ":D" & i).Value))

4. I used subsidiary array tbl and I join definite record to One String with separator "_":

w = Join(tbl, "_")

5. I found all Unique Strings using very useful Object in VBA: Collection.

6. At the End I wrote Unique Strings, in particular Unique records in array tblk, number 3 in the code

ReDim Preserve tblk(1 To 3, 1 To x)
For k = 1 To 3
  tblk(k, x) = tbl(k)
Next k

means three Columns. Best regards.

 

[Andrew Wilson]

ok, so i think i can see what the steps are although i dont know if i could replicate them
but it looks to me like the code removes any absolute duplicates where the 3 columns contain identical data. my challenge was to widen the parameter on the update column (Column 3 in the file i attached) which is a date time value like "10/10/2012 10:12:00" or 47911.3245879.

so records where name and Enq ID match and Update is within 5 mins of another record. would create a duplicate.