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.
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.
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.