Forum Discussion
Formula to reveal the row number of where the duplicate value is located
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.
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.
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.
- Excuse my ignorance, what is the formula in D2? That's what I am looking for. I tried the above formula and it's not working for me.
Have you tried opening the enclosed sample file? Does it work there?
Copy the formula from the enclosed XLSX file and adapt it to your needs.
- I had to save your file to retrieve. It's not working for me for some reason. The formula comes back with a zero value but the duplicate value is located on B653.
="B"&MATCH(1,COUNTIF(A:A,B:B),0)