Thread: Need help with finding particular duplicates

Hi vvk4,

I have tried to achieve this without vba and pivot.
Follow below steps to find out the solution through easy formulas where I have assumed that you have this data in column A :-
1) On the right of the last entry of column A i.e., "componenet Comp4", write following formula:-

=IF(ISNUMBER(SEARCH("assembly",A9)),A9,B8)

2) Again, on the right of above, write following formula:-

=IF(ISNUMBER(SEARCH("comp",A9)),A9,"")

3) Again, on the right of above, write following formula:-

=SUMPRODUCT(($B$1:$B$9=B9)*($C$1:$C$9=C9))

4) Copy all of these three formulas and fill upwards.
5) Now to know the correct Assemply name, use following formula which you can write any where you want:-

=OFFSET($D$1,MATCH(MAX(D1,D1:D9),D:D,0),-2)

Let me know if this works.

Regards,
DILIPandey