Jun 18 2024 08:49 AM
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.
Jun 18 2024 11:44 AM
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.
Jun 18 2024 11:54 AM - edited Jun 18 2024 11:55 AM
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.
Jun 18 2024 12:08 PM
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.
Jun 18 2024 12:23 PM
Jun 18 2024 12:27 PM
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.
Jun 18 2024 12:49 PM
Jun 18 2024 12:58 PM
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:
Jun 18 2024 01:03 PM
Jun 18 2024 01:18 PM - edited Jun 18 2024 01:24 PM
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?
Jun 18 2024 01:52 PM
Jun 18 2024 02:04 PM
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
Jun 20 2024 01:50 PM
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.
Jun 20 2024 01:52 PM
@dscheikey no need to get excited. I'm very busy and have a deadline to reach. I posted what worked so that should help.
Jun 21 2024 04:34 AM
I'm doing something wrong because the formula that works for you gives me a #N/A error.
It would be nice if you could spare some time again and provide some clarification.
Jun 21 2024 11:42 AM - edited Jun 21 2024 11:45 AM
@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.
Jun 21 2024 11:49 AM
Jun 21 2024 01:26 PM
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.
Jun 21 2024 02:11 PM - edited Jun 21 2024 02:11 PM
SolutionThank 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.
Jun 21 2024 02:11 PM - edited Jun 21 2024 02:11 PM
SolutionThank 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.