Finding duplicate records

[Susan]

Hello All,

Have 10 columns of data for each row(record). Dates, serial numbers, dollar
amts.
Sometimes a serial number is keyed twice. What is the best way to find if
there are duplicate serial numbers after keying about 500 records? Is there a
way to prevent?
Using Excel 2002.
Many thanks to all!

Susan

 

[Teethless mama]

In B1: =COUNTIF($A$1:A1,A1)>1
Copy down as far as needed.

AutoFilter the TRUE value, then go to Edit > Delete

 

[Tom Hutchins]

You can prevent duplicate entries using data validation. For example, assume
the first 500 serial numbers will be entered in cells A1:A500.
- select A1:A500
- select Validation from the Data menu
- select Custom from the 'Allow' dropdown box
- enter this formula:
=COUNTIF($A$1:$A$500,A1)=1
- on the Error Alert tab, enter the message you want displayed
- click OK to close the Data Validation dialog.

Data Validation will only catch duplicates entered manually by a user. It
won't catch duplicates created by VBA procedures, recalculations, or copying
& pasting.

Hope this helps,

Hutch

 

[Susan]

Thank you

In B1: =COUNTIF($A$1:A1,A1)>1
Copy down as far as needed.

AutoFilter the TRUE value, then go to Edit > Delete

 

[Susan]

Thank you

You can prevent duplicate entries using data validation. For example, assume
the first 500 serial numbers will be entered in cells A1:A500.
- select A1:A500
- select Validation from the Data menu
- select Custom from the 'Allow' dropdown box
- enter this formula:
=COUNTIF($A$1:$A$500,A1)=1
- on the Error Alert tab, enter the message you want displayed
- click OK to close the Data Validation dialog.

Data Validation will only catch duplicates entered manually by a user. It
won't catch duplicates created by VBA procedures, recalculations, or copying
& pasting.

Hope this helps,

Hutch