Formula To Identify A Cell Has Duplicate Words

[Admin]

Hi All,

Here is a method which gives 1 or 0 if a cell has duplicate word(s) in it.

I hope this helps you !

Enjoy !!

Sheet1

*	A	B
1	vista mac vista	1
2	windows linux	0
3	excel word excel	1
4	excel fox den of fox	1
5	admin excelfox	0
6	one two one one	1

Spreadsheet Formulas

Cell Formula B1 =SUMPRODUCT(--(FREQUENCY(MATCH(String,String,0),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(String))))>1)) B2 =SUMPRODUCT(--(FREQUENCY(MATCH(String,String,0),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(String))))>1)) B3 =SUMPRODUCT(--(FREQUENCY(MATCH(String,String,0),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(String))))>1)) B4 =SUMPRODUCT(--(FREQUENCY(MATCH(String,String,0),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(String))))>1)) B5 =SUMPRODUCT(--(FREQUENCY(MATCH(String,String,0),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(String))))>1)) B6 =SUMPRODUCT(--(FREQUENCY(MATCH(String,String,0),ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(String))))>1))

Sheet1

*	G	H
1	*	To define 'String'
2	*	Select A1(or whatever the cell your data starts)
3	*	*
4	String	=EVALUATE("{"""&SUBSTITUTE(Sheet1!$A1," ",""";""")&"""}")

Excel tables to the web >> Excel Jeanie HTML 4


Define String as

=EVALUATE("{"""&SUBSTITUTE(TRIM(Sheet1!$A1)," ",""";""")&"""}")