Duplicate Difficulties

[:: Amy ::]

Hello All,
I am working a list with over 40,000 rows...
I need to see if there is a way that can delete duplicate rows...
Now to complicate things... Have a list that has acct numbers and times...
but i want delete duplicates that are in the same 5 mins. Make sense???
Please Help!!!

 

[PJFry]

Couple of questions:

How are your timestamps formated? Are they a traditional stamp, like
4/28/2009 10:00 PM, or are they something custom

When you that you need to delete entries in the 'same five minutes', is this
a static window of time? For example if the time is between 10:00:00 and
10:04:59, then are these the same five minutes? 10:05:00 to 10:09:59 would
be the next, etc.

Let me know.

 

[:: Amy ::]

ok... here is an example of the info that i am working w/
Unfortunately when I export this report to excel the time stamp is like a
text...
I can fix that however, we are looking at about 40,000 rows

Employee Account # Date Time
John Doe 83773011 3/20/2009 4:46PM
John Doe 83773011 3/20/2009 4:27PM
John Doe 97868507 3/20/2009 4:26PM
John Doe 97868507 3/20/2009 4:25PM
John Doe 2433904 3/20/2009 4:19PM
John Doe 2433904 3/20/2009 4:18PM
John Doe 2433904 3/20/2009 4:18PM
John Doe 92396306 3/20/2009 4:15PM
John Doe 103451304 3/20/2009 4:08PM
John Doe 54459201 3/20/2009 3:56PM
John Doe 54459201 3/20/2009 3:52PM
John Doe 6101201 3/20/2009 3:39PM
John Doe 108010401 3/20/2009 3:24PM
John Doe 81673524 3/20/2009 3:15PM
Thank you sooooo much for your time... i appreciate any help that I can
get

 

[Herbert Seidenberg]

Excel 2007
Text to Columns, MRound, Remove Duplicates
http://www.mediafire.com/file/igjn2mwewtm/04_29_09.xlsx

 

[Dennis Tucker]

Are the duplicates next to each other(row to row)?

Dennis

 

[Dennis Tucker]

Are the Account#'s and the Time values in separate columns?

A B C D
John Doe 83773011 3/20/2009 4:46PM

If they are, I would do the following
1. Add a column on the end(right side) that contains the formula
=if(B1=B2,"Duplicate Account") note: lets say this is in cell E1 (are the
account numbers duplicated?)
2. Add another column on the end(right side) that contains the formula
=if(E1="Duplicate Account",D1-D2) note: lets say this is in cell F1 (if the
account numbers duplicated, what is the time difference?)
3. Add another column on the end(right side) that contains the formula
=if(F1<0:05,"Less Than 5 Minutes") note: lets say this is in cell G1 (if
the account numbers duplicated, is the time difference less than 5 minutes?)

Then use a macro to look through the G column and start erasing the bad
line(duplicate records under 5 minutes).

Dennis