Thanks for looking guys
I have a sheet with 10 columns and 1500 rows. what i am looking to do is a conditional format that only flags if 2 sets of duplicates exsist.
For example A1 has 1 And B1 has 11
Then A2 has 1 and B2 has 22. I dont want A1 And A2 to flag as a duplicate because B1 and B2 are different. Basiclly only flag if those two cells in a row match numbers throughout the sheet.
Thanks in advance for your help
Highlight only if conditional duplicates exsist
-
-
-
Re: Highlight only if conditional duplicates exsist
Select your range of cells.
Format > Conditional Formatting
Formula is: =SUMPRODUCT(($A$1:$A$17=$A1)*($B$1:$B$17=$B1))>1
Change the 17s to match the number of rows in your range. If your range changes then replace those two fixed ranges with dynamic ranges. -
Re: Highlight only if conditional duplicates exsist
Thanks Rob, but there is one tiny problem. How do i make it ignore blank cells? Here is the exact way i have it entered.
=(SUMPRODUCT(($F$7:$F$1500=$F7)*($H$7:$H$1500=$H7))>1 -
Re: Highlight only if conditional duplicates exsist
This should do it:
=(SUMPRODUCT(($F$7:$F$1500=$F7)*($H$7:$H$1500=$H7)*NOT(ISBLANK($F$7:$F$1500))))>1 -
Re: Highlight only if conditional duplicates exsist
Thats the ticket! It works like a charm. Thanks for your help Rob.
-
Re: Highlight only if conditional duplicates exsist
Just for future reference. Try to give more detail in your initial description/explanation. Better the detail, faster and more accurate the solution. Less work for the volunteer too.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!