Duplicate Values for specific ranges

[Ghesselink]

Hello,

I'm trying to remove duplicate values, however the problem is that by the usual conditional formatting method I will also delete duplicates I do not want to delete.

The datasets is at follows:
X, 100
X, 100
X, 100
Y, 200
Y, 200
Y, 200
Z, 100
Z, 100
Z, 100

And I want to get to
X, 100
X, 0/blank
X, 0/blank
Y, 200
Y, 0/blank
Y, 0/blank
Z, 100
Z, 0/blank
Z, 0/blank

The problem is, if I usually delete duplicates the '100' of the 'Z' row will be deleted as well.

Is there any way to solve this?

Thanks in advance,

 

[Joe4]

Do you physically need to remove/change the amount, or will just hiding them be sufficient?
If hiding them will be sufficient, you can use Conditional Formatting to make the text the same color as the cell background so that it becomes invisible.

So, if your data is in cells A1:B9, do the following:
- Select cells B2:B9
- Enter the following Conditional Formatting formula:

=AND(A2=A1,B2=B1)

- Choose the the formatting option of white font

 

[Ghesselink]

Yes, that is very helpful. I think just removing them would be possible with IF(B2 = B1, " ", B1).
I am not sure which of the two solutions will be the best for the bigger problem, but the hiding one is very helpful.

Thanks!

 

[Joe4]

Yes, that is very helpful. I think just removing them would be possible with IF(B2 = B1, " ", B1).

Because you are looking at two fields, not one, you would need to incorporate the exact same logic I did in the Conditional Formatting, i.e.

[COLOR=#333333]=IF(AND(A2=A1,B2=B1), "", B1)[/COLOR]