How to list Column without duplicates by formula

I suspect it is something to do with the length of the "numeric" strings. If I use Conditional Formatting, it highlights entries as duplicates even though they are not.

If you have, by any chance, upgraded to 365, you could use this:

Formula:

Please Login or Register  to view this content.

These are treated as duplicates:

190802203051017408042864
190802203051017408043425
190802203051017408043681
190802203051017408043796

Only this one is included using your formula

190802203051017408043796

It appears to be ignoring the last four digits. So, the unique part is being treated as 19080220305101740804

yes i can see that but i can't upgrade to 365 right now is there any fix to this formula to make it work for more that 20 characters or fix for the other formula

For Excel 2007+
If the differences between the cells mainly come from characters on the right, we can take up to 20 characters from the right to compare.
Like this:
F2:

Please Login or Register  to view this content.

Note: The 2nd INDEX helps to avoid Ctrl-Shift-Enter combination.

Thanks for your reply
good thinking
it is working by this way

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.