Actually, I already have a partial answer!!! Conditional formatting with “Cell value is” → “duplicate” !!! This way a check is performed for each user’s new entry in “real time”.
I need to check if duplicate entries exist in 30000 rows of a column (any value, but not blanks!) . I would like to keep track of how many duplicates during the filling process.
Ok, conditional formatting is a very effective visual indication and fast anough for my needs, but as I am not able to perform a loop to check the color of the cells (found some people against this approach!! Would be so easy! ) I need to find an alternative way to count the duplicates (as a whole, no need to identify how many for each case!).
I tryed the formula:
It works, but it takes two minutes to finish!!!
If you want to replicate my case. My 30000 entries are formatted as: letter “A” and numbers between 100000 and 999999, e.g., A354125, A214547, etc. Copy as text the result of “=CONCATENATE(“A”;RANDBETWEEN(100000;999999))” to save time.
PS: Does anybody know the algorithm used to find the duplicates in conditional formatting (it is fast)?
A macro solution is not the best, but is acceptable!