highlight duplicate names within single cell

Hi,
I am new to VBA trying highlight duplicates from a single cell,
I have range D6 to J75, each cell contains names separated by comma and space, and some times repeated for example D6 as Joes, Tony, Samuel, Joes, and E8 as Adrian, Sam, Prakash, Tony, After the macro: It should highlight Joes in D6 & Tony in E8,


Any help would be much appreciated, many thanks

Try this:-

Please Login or Register  to view this content.

Regards Mick

Hi,
Thank you very much, sorry one more help can I make this work automatic.

thanks a lot
regards
rao

You're welcome

Paste the code below into the active sheet code module.
Code runs when cell altered in range "E6:J75".

Please Login or Register  to view this content.

Regards Mick

sir,
thanks a lot,

regards,
rao

You're welcome

Please show a few rows example of your Data and the expected result.

hi,
At present I am using a formula:- =SUMPRODUCT(--(D6:D17<>""),LEN(D6:D17)-LEN(SUBSTITUTE(D6:D17,",",""))+1)-(LEN(D6:D17)-LEN(SUBSTITUTE(D6:D17, "(TE)","")))/LEN("(TE)") to get my result but it is very big & if there is no value I am getting 0, I do not want 0 as a result I want blank cell if value is 0,
to avoid all this am try to get vba codes to work, example I have a range e6:j17 filled with names separated by comma & space, few names ending with (TE)
which I don't want to count them, from E6 to J17, I have names like Joes, Tony, Samuel, Adrian(TE), Sam, Prakash, Tony I want result in O5 =6,
hope it is clear,
sorry if it not,
thanks,
rao

Sir,
I think, I have not example properly, in worksheet range E6:J17 their are few names separated by comma and space, I want to count number of names in that range, in the same range one or two names will ending with (TE), (TE) represents trainees, I don't want to count trainees, example from range E6:J17 I have names :- Joes, Tony, Samuel, Adrian(TE), Sam, Prakash, Tony total =7, but I want result as 6 because I don't want to count (TE). at present I am using formula to get this result {=SUMPRODUCT(--(E11:J17<>""),LEN(E11:J17)-LEN(SUBSTITUTE(E11:J17,",",""))+1)-(SUMPRODUCT(--(E11:J17<>""),(LEN(E11:J17)-LEN(SUBSTITUTE(E11:J17, "(TE)","")))/LEN("(TE)")))} it very big, can you please help me to get vba for the same.
thanks
rao

Any one please slove my problem.

hi,
I am sorry do not know this, please ignore my mistake, what is OP, what u want me do,

http://https://www.mrexcel.com/forum...ngle-cell.html

Thank you very much,

any one please help me.

Hi,
highlight duplicate names within single cell,
sorry i facing problems with space between the names, user are giving space as they like, some time's no space and more 4 spaces and chr(10)
so want to trim and clean the string, first loop is working well but second loop from (k in .key) get's hanging,
please help me.

Please Login or Register  to view this content.

Originally Posted by MickG

You're welcome

Paste the code below into the active sheet code module.
Code runs when cell altered in range "E6:J75".

Please Login or Register  to view this content.

Regards Mick

sir,
thank you very much for your above code,
it is working fabulous so for, recently I am facing one problem, if a name is repeats more then three times
it is highlighting other names also, like "mohan rao" repeated more then three time it is highlighting rao of other nmae like prakesh rao, please solve my problem.

regards
mohan

hi,
any one please help,
regards
mohan

Upload a sample workbook showing before/after.

dear sir,

1. list1
   FAIRUL
   SAMUEL
   NAVEEN JT
   HASSAN
   MOHD BILAL

1. list2
   RABI FAIRUL
   SAMUEL JT
   NAVEEN PRASANJIT
   HASSAN ALI
   BASHIR MOHD BILAL

sir,
list1 and list2 both are different names separated by comma,code suppose to highlight duplicate names only, but it is highlighting both names, I hope you understood,

regards,
mohan.

If you categorize list1 = E6:E21, you have Hassan and Hassan Ali highlighted.

Meanwhile, in list2 NAVEEN is not, even you have NAVEEN JT.

What is the difference and how you determine "duplicate"?

1) What do you mean list1 and list2? what are they?
2) What are Name1 & name2 in your workbook?

You said

all the names are separated by comma.

Not all...

sir,
name1,name2 and list1, list2 are same, this is for your reference, this names are highlighted in the workbook, I have written separately to explain to you,
please check the range ("D6:L19,D23:L43") names which are highlighted.
thanks

mohan

You partially highlighted ;

1) BASHIR MOHD BILAL
2) HASSAN ALI
3) SAMUEL JT

Why?

sir,
code is highlighting like that not me that is the problem, please check the code in sheet1 Worksheet_Change(ByVal Target As Range)
in sample workbook.
thanks
mohan

If so, upload a workbook before/after that you expect.
You should do it manually to show us what you have and what you expect.

Otherwise, wasting time.

If you just want to modify existing code, wait someone else.

I was to build the code from scratch. Good luck.

1) To Sheet1 code module

Please Login or Register  to view this content.

2) A standard code module

Please Login or Register  to view this content.

If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.