duplicate rows in excel database

G

graphic

Guest
Hi all,

Does anyone know a simple way to identify and remove duplicate rows from an Excel 2000 database? I've a database that's about 6000 rows long and 8 columns wide, and some of the rows are duplicates of one another. I really don't fancy the task of going through it all by hand.
 
Re: duplicates

you shouldnt be using excel as a database in the first place. think about using access instead, even for a simple single table dbase it is much better. access also has a find duplicates query wizard
 
Re: duplicates

you shouldnt be using excel as a database in the first place.
Click to expand...
Hi Eamonn,
I dont know about that. For single table lists Excel can be perfectly adequate.
ajapale

In the simple case where there are only two columns per record and 12 records try the following:

Enter the formula : =A1&B1 to cell C1 and copy / paste the formula to cells C2:C12* * * *

Enter the formula : =IF(COUNTIF($C$1:C1,C1)>1,"Duplicate","Unique")
* * * * to cell E1 and copy / paste the formula to cells E2:E12

Google Search Strategy ( microsoft excel "find duplicates" )
 
Re: duplicates

hi ajapale,
just to explain, the reasons i think excel shouldnt be used as a db.

it is very easy to select an entire column and then sort it thereby screwing up the whole thing.

data validation is limited and seldom used as it can be awkward to set up

most single table databases should really be split into multi tables if they were set up correctly. this is not something excel is designed for.


i should declare my vested interest here as i design db's for a living.
 
db design

Eamonn, do you not think excel is far easier for small DBs like office asset registers and the like of anything taking no more then 5-6k records.
A few locks on the sheet, a small form to enter the data and youre away. Its easier for users to query with the later versions of office too (conditional formatting etc). I dont know what its like now, but back in the day, I hated building anything in access.
caveat: Amonst other things Im in DB design too. (oracle and sql server)
 
Re: duplicates

Highlight your full list range and select Data -> Filter -> Advanced Filter.

Make sure the List Range is correct and select 'Unique Records Only'
 
You must log in or register to reply here.
Back
Top