Remove duplicates replace with

[theboyscout]

I need to remove all duplicates and replace the value with a new value:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]BLUE
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]BLUE
[/TD]
[/TR]
[TR]
[TD]3456
[/TD]
[TD]GREEN
[/TD]
[/TR]
[TR]
[TD]4567
[/TD]
[TD]YELLOW
[/TD]
[/TR]
[TR]
[TD]3456
[/TD]
[TD]GREEN
[/TD]
[/TR]
</tbody>[/TABLE]

What I'm looking in return is:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]MULTIPLE
[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]MULTIPLE
[/TD]
[/TR]
[TR]
[TD]4567
[/TD]
[TD]YELLOW[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance

 

[mrshl9898]

you could add column C

=IF(COUNTIFS(A:A,A2,B:B,B2)>1,"MULTIPLE",B2)

then pivot the data, id and new column in rows.