Hi
I have columns with NAMES and DOBS which i initially filtered by date order and used CF to show duplicates, due to it being a large sheet with many rows of school children in the same year there are a lot of duplicates, i would like to know how i can highlight the other columns if they also duplicate accordingly.
Thanks
Please see attached sample
Forestview
Use the formula in the conditional format
=COUNTIFS($A$13:$A$19,$A13,$B$13:$B$19,$B13,$D$13:$D$19,$D13)-1
Clever BMV but that would only work for duplicates so if someone shows up 3 times, it won't register. I'd go with
unless, of course, the OP is sure there are no triplicates.Formula:
=COUNTIFS($A$13:$A$19,$A13,$B$13:$B$19,$B13,$D$13:$D$19,$D13)>1
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
@ChemistB. - 1 or >1 it is the same because SUMIFS can't return 0, 1- 1=0 =False and 2,3.... -1=1,2..... looks like as TRUE.
Use the formula in the conditional format
=AND(A13<>"",SUMPRODUCT(--($A$13:$A$100&$B$13:$B$100&$D$13:$D$100=$A13&$B13&$D13))>1)
applies to =$A$13:$D$100
Thanks to you both for the assistance, i used your formula ChemistB as there may be triplicates
Kind Regards
Forestview
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
forestview 
Register To Reply
Bookmarks