Forum Discussion
Apply Conditional formatting for avoid Duplication
Hi,
You have a lot of exactly the same rules but applied only to some rows, not all of them
Usually one colour - one rule is enough. If you apply one rule
=$B4=OFFSET($B4,-1,0)
to entire column
it shall work. Please see attached.
Hi,
Could you please clarify a bit, you'd like to highlite duplications by conditional formatting and after that the person will do corrective actions; or you'd like to prevent with data validation?
- duplications of what?
- what prevents you to copy/paste the rows?
Hi,
Thank You for your replay,
Duplications of what?
I want to know the duplicate value of the MI Number
For example ,
I apply conditional formatting or data validation .
I copy the data of row number"2715" and paste the data on next row "2716"
at that time it does not working
Hi,
You have a lot of exactly the same rules but applied only to some rows, not all of them
Usually one colour - one rule is enough. If you apply one rule
=$B4=OFFSET($B4,-1,0)
to entire column
it shall work. Please see attached.
- Thankyou very much sir ,
This is work Please find the attached copy
Here are to points (please see column H in attached file)
1) Use formula rule (not predefined as Duplicate) to copy formatting correctly
I'd suggest
=COUNTIF($H$13:$H$1000,$H13)-1
which is applied to all your data range with some gap, I use $H$13:$H$1000
When you copy paste the row it will be formatted correctly, however each such copy/paste will add new rule even if the cell is within defined range, like this
To avoid this better to use Paste Special->Formulas & Number formatting
from ribbon or after pasting press Ctrl and select same icon.
When no new rule appears
If do so your Duplicates rule also will work, however with the formula rule you don't lost formatting if forgot Paste Special.
Alternatively you may change registry as described here https://support.microsoft.com/en-us/help/973823/conditional-formatting-rules-are-duplicated-when-you-copy-and-then-pas but I didn't test that.
