Forum Discussion
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.
- dscheikeyBronze Contributor
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.
- ANGHamilton777Copper 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.- dscheikeyBronze Contributor
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.