Need help with finding particular duplicates

[vvk4]

I have the following data in Excel, rows arranged as follows:
Assembly Part1
Component COMP1
Component COMP2
Assembly Part2
Component COMP1
Component COMP1
Component COMP3
Assembly Part3
Component COMP4

Need to find all Assembly's that have more than 1 of same component which in this case is:
Assembly Part2 having Component COMP1

[dilipandey]

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