Share via

Long Number formatted as text - How do I check for duplicates (Excel)

Kimberly 21 Reputation points
2021-02-11T16:47:49.58+00:00

I have 16 numbers that have to be formatted as text to display in Excel. What can I do to check for duplicates when they are all in the same column?

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Accepted answer
  1. Lz._ 9,016 Reputation points
    2021-02-11T17:20:24.09+00:00

    Hi @Kimberly

    66983-demo.png

    In B2 (and copy down):
    =SUMPRODUCT(--(A$2:A$17=A2)) > 1

    NB: Be carreful with SUMPRODUCT, avoid something like:
    =SUMPRODUCT(--(A:A=A2)) > 1

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Emily Hua-MSFT 27,796 Reputation points
    2021-02-15T09:39:02.997+00:00

    @Kimberly
    Please refer to Lz-3068‘s answer.

    In addition, I would provide another formula based on Lz-3068‘s sample.
    You may add an auxiliary column, enter the formula in B2 to mark the repeated text.
    =IF(COUNTIF(A:A,A2&"*")>1,"Repeated"," ")

    Then you can use Filter to select duplicate rows.

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.