Check duplicates within a group

[Ehtisham Ali]

Hi Ninjas,

I am posting a after a long time because I have stuck up badly this time.

Please refer to the attached sheet.

My question is to find and mark duplicates within a group.
For the same "CODE" and for the same "first name" it is not a "Hit".
but for the same "CODE" and unique "first name" it is a "Hit".

I have also maually marked the results in sheet which i needs to be calculated through some formula because I have a very huge data.


It is urgent guys please help and provide solutions ASAP.

Thanks
Ehtisham

 

[Somendra Misra]

Hi Ali,

Can you try below formula in C2 and copy down.

=IF(SUM(--(FREQUENCY(IFERROR(MATCH(LEFT(IF(A2=$A$2:$A$9,$B$2:$B$9),FIND("|",SUBSTITUTE(B2&" "," ","|"))-1),LEFT(IF(A2=$A$2:$A$9,$B$2:$B$9,0),FIND("|",SUBSTITUTE($B$2:$B$9&" "," ","|"))-1),0),"e"),ROW($B$2:$B$9)-ROW($B$2)+1)>1)),"","X")

See the file with yellow cells.

Regards,

 

[Ehtisham Ali]

Thank you Somendra for your solution.
It is working fine as far as I check now but It seems very complex to me, so can't you make it simpler by making a helper col for splitting the first names only and then some formula.

Thanks

 

[Ehtisham Ali]

I have put more data into it and it is not working may be I have did something wrong can u plz suggest.

 

[NARAYANK991]

Hi Ali ,

See if the formula in column E works correctly.

Narayan

 

[Somendra Misra]

See this file, orange cells, is this working.

Regards,

 

[Ehtisham Ali]

Hi Somendra thank you very much for your awesome solution and prompt reply.

But solution by Narayan is more easier for me to understand thank you Narayan.

Does'nt matter for which solution I choose but I learn from every one of it.

Thanks again both you it is really a great help.