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



":: Amy ::" <:: Amy wrote in message
...
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 :)
--
Still Learning...


"smartin" wrote:

:: Amy :: wrote:
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!!!

Hello,

Consider this example in A1:C15

Acct Time Dups
J 17 0
E 1 1
K 3 0
B 18 0
K 19 1
J 7 0
E 6 2
G 4 0
F 9 0
C 6 0
E 11 2
K 16 1
E 16 1
B 12 0

Columns A and B are random data. The formula in C2 and filled down is:
=SUMPRODUCT((A2=$A$2:$A$15)*(ABS(B2-$B$2:$B$15)<=5))-1

A "Dups" value greater than 0 means the Acct has at least one other row
matching Acct with a Time within 5 units.

(I am using whole "Time" numbers to simplify the explanation. Since you
are probably working with a date/time-type field you will need to adjust
the comparison
<=5
to something like
<=(5/24/60)
in order to convert to Excel's date/time numbering.)

But before you sort on Dups and delete 0 I would draw your attention
to the special case of Acct "E".

Acct "E" has times of 1,6,11 and 16; each is within 5 units of some
other. However, the total Time span of this Acct is 15. Do all these
count as duplicates?