Conditional formatting based on 2 criteria

[beerw0lf]

Hello All
I'm sure there is an easy fix for this but I'm banging my head against the wall trying to figure out what it is. I have data that occupies several columns. There are lines that are duplicated. I would like to either copy all duplicated lines to a different worksheet or use conditional formatting to highlight all duplicated lines. Any help would be greatly appreciated.

Thanks

 

[WinteE]

Hi beerwOlf,

Add =SUMPRODUCT(($A$1:$A$100=content_colA)*($B$1:$B$100=content_colB)*1) to a new (hidden) column and copy it for all lines. This function works like a multiple COUNTIF().

Ones you made this column you can make a conditional format on either this new column or all cells in the row.

Erik

 

[beerw0lf]

I don't think that is what I was looking for. Let me try top explain better. I have headers at the top of columns A thru Z. The rows (or records) are being filled by pulling data from a type of DB when I run the report. Somehow I am getting records that are duplicated. Instead of looking through every record to see if there are an duplications I would like to have conditional formatting seek them out for me and highlight them. Column A and D are the 2 columns that I need to compare off of. I can run the Advanced Filter option but that just hides the duplicates. I was to be able to see them and make notes as to which ones they are. I hope this is a better explanation.

Thanks

 

[xenou]

Hi:
If you can get your data into Microsoft Access (from Access, GET EXTERNAL DATA...) you can run a find duplicates query with the query wizard.

----------------------------------------------------------------------------------
Note: If you can change the query itself that you are basing your report on, you might be able to fix the problem by adding the keyword DISTINCT to the query statement:

SELECT DISTINCT table1.Field1, table2.Field2, etc....
rather than:
SELECT table1.Field1, table2.Field2, etc....

Hope this helps

 

[gardnertoo]

I've got what you're probably looking for. The following TWO formulas entered in the conditonal formatting boxes look for duplicate lines in which both cell A# matches A## and D# matches D##. That was in case you might ever have the same item in column A with a different value in column D. The way I built this, I used the first condition to turn the cell green; this highlights the first occurance of an entry duplicated further down the sheet. The second condition I used to turn the cell red; indicating a duplicate entry's second or subsequent occurance.

Condition 1:

=IF(AND(COUNTIF($A$2:$A$100,$A2)>1,COUNTIF($D$2:$D$100,$D2)>1,COUNTIF($A$2:$A2,$A2)=1,COUNTIF($D$2:$D2,$D2)=1),1,0)

Condition 2:

=IF(AND(COUNTIF($A$2:$A$100,$A2)>1,COUNTIF($D$2:$D$100,$D2)>1),1,0)