Conditional Formatting, find duplicates with multiple columns

D

Dulanic

New Member
Joined
Mar 3, 2010
Messages
14
Hello,

I am wondering if I am way off base here. I am trying to do conditional formatting IF A, C, and E are all a duplicate of another. I was thinking...

=AND(AND(COUNTIF($A:$A,A2)>1,COUNTIF($C:$C,C2)>1),COUNTIF($E:$E,E2)>1)

But this does not work because it is just checking that if they are ever duplicates one at a time, where as I need it to check if they are a duplicate together not one at a time. Does that make sense?
 
Sort by date Sort by votes
WaterGypsy said:
try

=SUM(COUNTIF($A:$A,A2),COUNTIF($C:$C,A2),COUNTIF($E:$E,A2))>1
Click to expand...

That gives me the same net effect as what I posted.

If A and E are duplicated but not C, it will still count it as a duplicate. I need it to only consider it if basically A2&C2&E2 are all a duplicate of another A2&C2&E2.... I guess I could add a column for literally that couldn't I now that I think of it.....
 
Upvote 0
So I tried that...now the question is why does scrolling become slow as hell and excel stutters while scrolling. It isn't a complicated formula... And how can I work it in not to highlight blank cells?
 
Upvote 0
Thanks for getting my brain on track :) Getting closer now. M is A3&C3&E3 and the conditional formatting is applied to C3:C500 and it spead it up now.

=IF(C3<>"",COUNTIF(M3:M200,M3)>1)

Now the question becomes... how do I make it highlight the last duplicate found not the first duplicate found? Or highlight all duplicates found. I want it to add the highlighting as they enter the information.
 
Upvote 0
Brain farts today :( lol, I am solving this myself on the board, but maybe someone else may find the same issue this way and be able to solve it.

=IF($C3<>"",COUNTIF($M$3:$M$200,$M3)>1)

All set now. Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

kristell
  • Solved
Duplicate values on different tabs
Replies
3
Views
172
kristell
kristell
Ramadan
  • Solved
How to find duplicate values in one column while checking if a condition in another column is met
Replies
8
Views
493
Ramadan
Ramadan
G
  • Question Question
Find Duplicate Values using a Criteria
Replies
2
Views
266
Eric W
Eric W
Ramadan
  • Solved
Find Duplicates and change font color based on other cell value
Replies
3
Views
408
etaf
etaf
K
  • Solved
Conditional Format - Highlight Cell Based on Date
2
Replies
10
Views
465
DanteAmor
DanteAmor

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top