Formula to identify repeated words in the column

[rajaniesh]

Hi,

I am new to excel formula and need quick help here.

We have one spreadsheet shared with users where they store semicolon separated 8 character long Workitems in column D. for example

xabcd1234;efgh1234;

We need to implement two conditions :
Condition1: If users store the Workitems without embedding semicolons between workitems then cells should turn yellow

condition: If Workitem already exists in another cell and then if user store same Workitem in another cell in D column then both cells should turn red. To explain it more I am illustrating it in the table:
D Column
abcdefgh;12345abcd;RED because D1 's abcdefgh repeated in D2
abcdefgh;7845abcf;RED because D2 's abcdefgh repeated in here
9876efrd;9876efrd;RED because same WI is repeated in the cell

Let me know if there is more innovative way for this. Can we use some conditional formatting to achieve it.

Regards
Rajaniesh

 

[xlstime]

Hi Rajaniesh,

It is little complicate do this, have used 2 helper column to do this, please take reference from uploaded file.

Hi,

I am new to excel formula and need quick help here.

We have one spreadsheet shared with users where they store semicolon separated 8 character long Workitems in column D. for example

xabcd1234;efgh1234;

We need to implement two conditions :
Condition1: If users store the Workitems without embedding semicolons between workitems then cells should turn yellow

condition: If Workitem already exists in another cell and then if user store same Workitem in another cell in D column then both cells should turn red. To explain it more I am illustrating it in the table:
D Column
abcdefgh;12345abcd;RED because D1 's abcdefgh repeated in D2
abcdefgh;7845abcf;RED because D2 's abcdefgh repeated in here
9876efrd;9876efrd;RED because same WI is repeated in the cell

Let me know if there is more innovative way for this. Can we use some conditional formatting to achieve it.

Regards
Rajaniesh