Finding duplicates (2000)

Topic

New Reply

Hi,
I have an Access database that has a primary number called URN.
I am trying to import data from an excel sheet (an old database), into Access. An issue keeps arising where it is finding duplicate values in the URN field in the excel spreadsheet. Is there a formula I can do to highlight all duplicate values in the excel sheet, so as to amend them?

Reply | Quote

Replies

Edited to correct typo – thanks to Reimer for pointing it out.

Say that the URNs in your spreadsheet are in A2:A100 (A1 is the field name).
In cell B2 (or in the cell in row 2 in another column), enter this formula:
=COUNTIF($A$2:$A$100,A2)>1
and fill down as far as needed.
The result of the formula will be TRUE if the value in column A has duplicates, FALSE if it is unique.

As an alternative, you can import the Excel sheet into a new table in Access without setting a primary key on URN. Then, use the Find Duplicates Query Wizard to create a query that returns the duplicate values.

Reply | Quote

Hans,

I think the formula has to be changed to work properly (change the A1 to A2).
It should be =COUNTIF($A$2:$A$100,A2)>1.

Neat formula.
Regards,

Reply | Quote

Yes, thanks. I will correct the formula.

Reply | Quote

Yes, thanks. I will correct the formula.

Reply | Quote

Hans,

I think the formula has to be changed to work properly (change the A1 to A2).
It should be =COUNTIF($A$2:$A$100,A2)>1.

Neat formula.
Regards,

Reply | Quote

Edited to correct typo – thanks to Reimer for pointing it out.

Say that the URNs in your spreadsheet are in A2:A100 (A1 is the field name).
In cell B2 (or in the cell in row 2 in another column), enter this formula:
=COUNTIF($A$2:$A$100,A2)>1
and fill down as far as needed.
The result of the formula will be TRUE if the value in column A has duplicates, FALSE if it is unique.

As an alternative, you can import the Excel sheet into a new table in Access without setting a primary key on URN. Then, use the Find Duplicates Query Wizard to create a query that returns the duplicate values.

Reply | Quote