Forum Discussion

ANGHamilton777's avatar
ANGHamilton777
Copper Contributor
Jun 18, 2024

Formula to reveal the row number of where the duplicate value is located

Please help!  Last week I found an excellent formula and now am kicking myself for not saving it.  

 

I'm working on a large spreadsheet.  On 2 columns, I have run the Conditional Formatting / Duplicate Values and highlighted the duplicates.  I need the formula to add the Row Number where the duplicate is located.

 

Example: 2 columns referred to as A and B.  In column C, I want to add the formula that shows the duplicate value is on B20001.

  • Thank you. This is a perfect match =IFNA("B"&XMATCH(A2,B:B),"")

    ="B"&MATCH(1,COUNTIF(A:A,B:B),0) keeps having me reboot Excel due to a delay, not sure what the reason is. I'll go with the one above.

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    ANGHamilton777 

    If your data is in the range up to row 22000, then the following formula will give you the first row number where a duplicate was found in column A.

    =MATCH(1,COUNTIF(A1:A22000,B1:B22000),0)

    If there are several duplicates, you can use the following formula to create a list of duplicate lines:

    =FILTER(SEQUENCE(22000),COUNTIF(A1:A22000,B1:B22000)>0)

    See also my enclosed example.

    • ANGHamilton777's avatar
      ANGHamilton777
      Copper Contributor

      I appreciate your reply. But what I am looking for is the actual row number displayed. Say for instance, in Column A the value is on A2, and Column B the duplicated value is on B1000. In Column C, I want "B1000" to be displayed.
      Trust this makes sense.

      • dscheikey's avatar
        dscheikey
        Bronze Contributor

        ANGHamilton777 

        Then all you have to do is write a "B"& in front of it:

        ="B"&MATCH(1,COUNTIF(A1:A22000,B1:B22000),0)

         

        I have now written mach letters in columns A and B so that the content is not confused with the row number.

Resources