Replace duplicate values with zero or blank

[aris]

Dear friends

I have two columns A and B. Column A has names ans column B numbers.
Example: Suppose that the name PETER appears in colmn A ten times. The corresponding numbers in Column B are 2 5 9 5 5 12 5 4 8 4. Now I would like to make column C and D. Column C must have the name PETER 10 times as in A and coumn D must have the same numbers but where we have duplicate valus to be replaced by blank or zero. the results i would like to have is
C D
Peter 2
Peter 5
peter 9
peter 0 or blank
peter 0 or blank
peter 12
peter 0 or blank
peter 4
peter 8
peter 0 or blank

Can you please help on how to create columns C and D?
Is there any function to do this?

Thank you very much!

Aris

 

[fairwinds]

Hi,

Here are 2 options:

If you want to compare only the number:

D2: =IF(COUNTIF($B$1:B1,B2),"",B2)


If you want to compare both name and number:

F2: =IF(SUMPRODUCT(--($A$1:A1=A2),--($B$1:B1=B2)),"",B2)

Book1
	A	B	C	D	E	F
1
2	Peter	2		2		2
3	Peter	5		5		5
4	peter	9		9		9
5	peter	5
6	peter	5
7	peter	12		12		12
8	peter	5
9	peter	4		4		4
10	peter	8		8		8
11	peter	4
Sheet4