Coding Duplicate Records

[HWmR]

How can I code that a record is to be "K" kept or "D" deleted when
compared to its next row? Example: A sorted file by Address

BEFORE
LastName Address Sales
Date Keep/Delete
1. WASSEM 0N642 TITUS PL 67,500 6/25/2001
2. HART 0N655 E WEAVER CIR 70,000 3/19/2001
3. HART 0N655 E WEAVER CIR 40,000 3/19/2001
4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998
5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000
6. BLAND 0N670 GREEN PL 25,000 3/20/2000

AFTER
LastName Address Sales
Date Keep/Delete
1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 K
2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 K
3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 D
4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 D
5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 K
6. BLAND 0N670 GREEN PL 25,000
3/20/2000

Reasoning:
Wassem 1. not equal to Hart =K
Hart 2. = Hart 3. 70K> 40 & Latest Date =K
Hart 4. not equal to Poyth BUT should be =D because only one "Hart" can
be coded =K

Can This be done with cell formula in "Keep/Delete" or is this a task
for a de-dupe facility & the way to go? I need to use the K records as
well as analyze the number of D & K to measue file integrity. Thanks

 

[Bob Phillips]

Here is a formula for the first row, just copy down

=IF(COUNTIF($A$2:$A$8,A2)=1,"K",IF(AND(C2>C3,SUMPRODUCT(--(A$1:A1=A2),--(E$1
:E1="K"))=0),"K","D"))

--

HTH

RP
(remove nothere from the email address if mailing direct)