Find and replace with multiple values, finding duplicate values

[Jonathan Jones]

Hi,

I have two issues I need some help with. The first is: I have a list of old part numbers in column A and a list of new part numbers in column B. I would like to find and replace all instances of any of the old part numbers in column C with the relevant new part number.

Secondly, I have a list of part numbers in column A and in column B is a list of the components which are used to make up that part number. I would like to find instances where the component part number has been duplicated. Example below:

Column A Column B
TABLE TABLE-LEG1
TABLE TABLE-LEG1
TABLE TABLE-TOP1
TABLE2 TABLE-LEG1
TABLE2 TABLE-TOP2

It's the part numbers in column A which have a duplicated component which I am trying to find. I basically need a list of part numbers with duplicated components. So, if I ran the code on the above data, the result I would get is just "TABLE", as TABLE2 has no duplicated components. Clear as mud?

Any help would be very gratefully received.

J

 

[Jonathan Jones]

Hi,

I don't suppose anyone has any ideas?

J

 

[kvsrinivasamurthy]

For second problem.
In C2 then copy down.

=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1,"Duplicate","")