Forum Discussion
Solved
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 / Dupl...
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.
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)In some older Excel versions, the formula must be entered as a matrix formula. The formula is entered with CTRL + SHIFT + ENTER.
Which programme are you using? If Excel, which version?
e.g. LibreOffice Calc: