Finding duplicate records
 

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

Finding duplicate records
 

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

AutoFilter the TRUE value, then go to Edit Delete


"Susan" wrote:

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

Finding duplicate records
 

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" wrote:

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

Finding duplicate records
 

Thank you

"Teethless mama" wrote:

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

AutoFilter the TRUE value, then go to Edit Delete


"Susan" wrote:

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

Finding duplicate records
 

Thank you

"Tom Hutchins" wrote:

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" wrote:

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