CODEWITHSUNDEEP
exceltextduplicatesrow
user1721230
user1721230

Reputation: 315

Find duplicate text in a row with formula in excel

I have a spreadsheet and I need to find duplicate names in a row and flag it up in the cell starting that row, as well as that some names will be followed by a third word, so the formula will need to only look at the first 2 words in the cell. In the below example each item is a cell in a row:

Duplicate found, Jim Table, Felix Laptop, Tommy Window, Jim Table (Science), Donny Foot

The first cell has highlighted the duplicate "Jim Table" even though the second occurrence has "(Science)" on the end. What formula could I use for this?

I tried the below but it only seems to work with numbers.

=IF(COUNTIF($D$7:$Z$7,MODE($D$7:$Z$7))>1,"List has duplicates","No duplicates")

I really want to avoid making a UDF as it will be used by many users on separate computers.

Upvotes: 0

Views: 9322

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Try this version

=IF(SUMPRODUCT((COUNTIF(D7:Z7,LEFT(D7:Z7,FIND(" ",D7:Z7&" ",FIND(" ",D7:Z7)+1)-1)&"*")>1)+0),"List has duplicates","No duplicates")

The COUNTIF criteria part with LEFT/FIND/FIND finds the first two words of each cell, so if those repeat anywhere the COUNTIF returns a number >1 and SUMPRODUCT returns a positive, triggering the "List has duplicates" message

Upvotes: 3

Related Questions