Share via

Failed to highlight duplicate values in Excel

Anonymous
2019-05-10T16:46:36+00:00

if we apply conditional formatting for highlighting duplicate values, it is not working for below two values.

145569444732689024318601777155 145569444732689054318601777155

When Excel going to fix this? these values in text format.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2019-05-12T16:26:06+00:00

    You are right, Excel highlight that as duplicates, because within the default conditional formatting rule Excel converts text that looks like numbers as numbers.

    Within Excel, the same thing happens as if you apply this formula:

    B1: =VALUE(A1)

    As you can see in the screenshot below, the numbers becomes the same. This is due to the max. possible precision.

    https://support.microsoft.com/en-us/help/78113/...

    You can avoid that if you add any non numerical char to the number (at front or end).

    C1: ="#"&A1

    Now Excel "sees" the text as text, does no conversation and compares the strings.

    Andreas.

    20+ people found this answer helpful.
    0 comments
  2. DaveM121 871.3K Reputation points Independent Advisor
    2019-05-10T17:32:15+00:00

    Hi sakthis1

    That is because those two numbers are not the same, see below:

    .

    6 people found this answer helpful.
    0 comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2019-05-11T08:46:08+00:00

    You can use a formula and compare the strings to determine how many percent of A1 match A2

    B1:  =SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=MID(A2,ROW(INDIRECT("1:"&LEN(A1))),1)))/LEN(A1)

    So if you sort your data you can use it to find rows that are similar to the next one below.

    Andreas.

    5 people found this answer helpful.
    0 comments
  4. Anonymous
    2019-05-12T15:50:44+00:00

    Thanks for your reply,

    yes ! you mentioned correctly . there is difference in those numbers. But excel highlighting as duplicated!

    3 people found this answer helpful.
    0 comments
  5. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2019-05-10T16:49:34+00:00

    Hi sakthis1

    Greetings! I am Vijay, an Independent Advisor. I am here to work with you on this problem.

    This is working perfectly fine. See the picture.

    If you share you file through Onedrive where this is not working, I can point out the issue. Don't upload any confidential / sensitive data.

    Do let me know if you have any more question or require further help.

    2 people found this answer helpful.
    0 comments