highlight all repeat datas in a worksheet

[vcff]

Hi All,

I have a worksheet with about 400 rows and 20 columns of datas.
How can I check to ensure that there are no repeat datas in the worksheet?
Your help will be appreacited.
Thanks.

R

vcff

 

[Gaurav]

following link should help

http://www.cpearson.com/excel/Duplicates.aspx

 

[vcff]

It's only for single column or row.
What I need is to check the whole worksheet for duplicate data. Is there any
way to do it?

Tnks

 

[Duke Carey]

It's an enormous task in Excel prior to 2007. You can create a copy of your
data and then try to use the Remove Duplicates command in 2007. In a quick
test it worked with around 60 columns, so it may work on 200 columns, too.

If you have MS Access you could import the data to Access, then set the
primary key to be all the columns. If there is any row that duplicates any
other, Access will refuse to create the index, though that wouldn't, by
itself, identify the duplicated data.

 

[vcff]

OK will give a try.

Tnks for the reply.

vcff

It's an enormous task in Excel prior to 2007. You can create a copy of your
data and then try to use the Remove Duplicates command in 2007. In a quick
test it worked with around 60 columns, so it may work on 200 columns, too.

If you have MS Access you could import the data to Access, then set the
primary key to be all the columns. If there is any row that duplicates any
other, Access will refuse to create the index, though that wouldn't, by
itself, identify the duplicated data.