Advanced filter and Conditional Format

oric

New Member
Joined
Jul 19, 2005
Messages
36
Hi All,

I have a whole bunch of data (~7000 rows and 40 columns).
I have recently found that some of the row entries are duplicates. I have been searching on the boards on how to deal with these duplicates. Since I am looking for duplicate rows the usual countif stuff hasn't worked well for me. I think by using the advanced filter with the "unique records" box checked will work. The only problem is that I don't know which ones were the duplicates. I need to know this for future work.

Is there a way I can combine the conditional formating w/ the advanced filter so that the non-unique records can be highlighted??

Is there another way that is easier??

Many thanks in advance...
 

mikebecker

Board Regular
Joined
Mar 28, 2004
Messages
227
use this formula in conditional formatting where Range is where you are looking for none unique values. B1 is the first cell in the range.

=IF(COUNTIF(Range,B1)=1,FALSE,TRUE)
 

oric

New Member
Joined
Jul 19, 2005
Messages
36
Thanks.

Unfortunately that didn't really work. By using countif I am looking at every instance where a certain value occurs. I am trying to find when certain rows occur.

My data looks something like

A B C D
1 4 5 9
4 3 5 8
1 2 3 9
1 4 5 9

As you can see numbers readily occur but the first and the last row (the duplicates) are what I am trying to find in this database. The advanced filter with unique records took the rows out but I am just trying to find a way to see where the duplicates were without having to scan my 7000 line file and look where I see a number jump in filter mode.
 

Forum statistics

Threads
1,077,784
Messages
5,336,317
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top