Conditional Formatting Based On Two Duplicates

[kescco]

Hello,

I have tried to figure this one out on my own but cannot quite get it right. If you know the answer, please reply.

In a spreadsheet for my finances, I need to highlight all rows which have both the same date and same amount.

The entries are sorted. The DATE is COLUMN D. The AMOUNT is COLUMN I.

Thanks,

Kescco

 

[AlphaFrog]

Select all your data with A1 being the active cell. Then try this Conditional Formatting formula...

Formula is =AND(COUNTIF($D:$D,$D1)>1,COUNTIF($I:$I,$I1)>1)

 

[jasonb75]

Alpha, I was thinking similar, but that would still count even if the duplicate date and value were on different rows.

My suggestion would be =COUNTIFS($D$D,$D1,$I$I,$I1)>1

But that's no good with pre 2007 versions of excel.

 

[Marcelo Branco]

Hi,

Maybe this, assuming data in D1:I100

=SUMPRODUCT(--($D$1:$D$100=$D1),--($I$1:$I$100=$I1))>1

HTH

M.

 

[kescco]

I will try the latest one seeing I am using 2003. Honestly, I do not like the GUI of 2007 and later but may end up there soon anyway.

Kescco