SOLVED

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

Copper Contributor

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.

18 Replies

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

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.

@ANGHamilton777 

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

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

 

dscheikey_0-1718737561000.png

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.

@ANGHamilton777 

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)

@ANGHamilton777 

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:

dscheikey_0-1718740654391.png

 

@ANGHamilton777 

There can't be a zero value! There is no such thing as a zero line. If there are no doubles, the formula produces an error. Can you attach a screenshot like I did. You should be able to see the input line and the cell with the formula.

 

Please also look at the formatting of the cell. Is it set to General?

I found a formula that worked. I appreciate your time and assistance. Thank you

="B"&MATCH(A2,B:B,0)

@ANGHamilton777 

This will only bring you the row in column B that is identical to cell A2. Of course, if you already know that the cell with the duplicate entry is in cell A2, then it can work. My formula also works. Please send a screenshot. PLEASE PLEASE PLEASE

@ANGHamilton777 

My dear! On a community site, we all want to learn from each other. Users who follow us here want to understand which is the right formula for the problem you have described. Find a duplicate entry from column A and column B. The formula should bring the cell in column B where the duplicate entry is to be found.

I have created a formula with MATCH() and COUNTIF() that works in Microsoft 365 and Excel for the web. The formula does not work for you. To find out more precisely why the formula does not work for you, I have asked you to upload a screenshot here. Other users should learn under which circumstances the formula does not work.

Unfortunately, you don't seem to be willing to participate in the troubleshooting. On the one hand, you want help from the community. On the other hand, you are not prepared to give something back to the community.

I therefore ask you again to help with the clarification.

 

 

@dscheikey no need to get excited.  I'm very busy and have a deadline to reach.  I posted what worked so that should help.  

@ANGHamilton777 

I'm doing something wrong because the formula that works for you gives me a #N/A error.

dscheikey_0-1718969490125.png

It would be nice if you could spare some time again and provide some clarification.

@dscheikey Ok I have a few minutes, here's what I get. On the top is the formula you suggested where it gives me a "0" return. On the one I posted, I also receive the "N/A" on some of them.

ANGHamilton777_0-1718995356804.png

 

In my case, the N/A refers to "Not Found", no duplicate. Copilot suggested I use this formula: =IFNA(VLOOKUP(A49, B:B, 1, FALSE), "Not Found")

@ANGHamilton777 

Good that you have now shown the screenshot here. Now I've realised that I misinterpreted your question. I understood that you want to find the (singular) duplicate value that occurs in both column A and column B. From the image I interpret that there are several duplicate entries and you want to find the duplicate value in column B for each value in column A. This was not clear from your description and questions.
The correct way is to write the formula in cell A2 and then copy it down so that each row refers to the cell in column A.
The formula 

=IFNA("B"&XMATCH(A2,B:B),"")

is the right one for this task.
Please note that only the first hit in column B is shown.

 

What I find very irritating is your first screenshot with the "0" as the result. As already mentioned, this cannot be correct. Since the formula starts with "B"&, the result must be a text also start with "B". The only way to achieve the 0 there would be if the formatting of this cell is user-defined and the format function says something like 0,0,0,"0". Then there would always be a 0 in this cell when there should actually be a text there. Can you check whether the formatting of the cell is user-defined?

 

According to your screenshot, B1 should actually be the result there, as your headings in columns A and B are already identical.

best response confirmed by ANGHamilton777 (Copper Contributor)
Solution

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.

1 best response

Accepted Solutions
best response confirmed by ANGHamilton777 (Copper Contributor)
Solution

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.

View solution in original post