Help needed finding duplicate words with single text cells in google sheets?
Data has trim function applied. e.g =if(B10"",""trim(concatenate($I$3," ",trim(B10)," ",$J$3))) Tested solution needs some modifications
Requirement number of duplicates in one cell in not important, it can be any word, in any position, it has to match exact word and duplicates should not be removed.
For example, in a cell: "Great Great Expectations" formula should detect “Great” repeated twice, and where no duplicate words are found no change should happen
Desired outcome would be to highlight entries with duplicate entries in column "I" or create message box listing duplicate entries. Tested solutions include vlookup and array formula as standalone "dupe" "no dupe" comparison. Only Solution that provide any success was
=IF(SUM(N(IFERROR(FIND(" "&MID(A2,ROW(OFFSET(A$1,,,LEN(A2))), MMULT(FIND(" ", {""," "} &A2&" ",ROW(OFFSET(A$1,,,LEN(A2)))),{1;-1}))&" ", " "&A2&" "), LEN(A2)+1)<ROW(OFFSET(A$1,,,LEN(A2)))))>0, "Dupes", "All good")
This return consistently correct result for standalone data, but I have not sure how to modify this to. Run automatically Highlight Cells with Duplicates
Open to any solutions
Thanks in advance
@Harun24Hr
Array Formula Returns "1" for blank lines and Ref range error for any other cell.
Conditional Formatting is applying Colour formatting to entire range, with data no longervisible. Any ideas
Thanks
