I have a list of numbers and I want to highlight duplicates.
It's already working in its current state with conditional formatting and a custom formula: =COUNTIF(B:B;B1)>1
However, I would like to improve on that.
I only want it to highlight the cells value, if the duplicate is within the last 150 rows of that column, or after the current cell.
I tried the first part with going backwards, like =COUNTIF(B1000:B850;B1000)>1, but the range always gets re-ordered again.
You may try with:
Apply to range: B:B
Custom formula is:
=countif(index(B:B;max(1;row()-149)):index(B:B;row()+149);B1)>1