Advanced filter and Conditional Format

[oric]

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]

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]

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.

 

[mikebecker]

You could concatenate the data and then apply the above conditional formatting to not only to Column E but the columns with the data.

Book1
	A	B	C	D	E
1	1	4	5	9	1459
2	4	3	5	8	4358
3	1	2	3	9	1239
4	1	4	5	9	1459
Sheet1