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,
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.
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.
- Thank You sir giving the valuable information
Is this formula is applicable to the above excel worksheet. "NORMAL CERTIFICATE"
For finding the Duplication
- Thankyou very much sir ,
This is work