# Duplicate Values for specific ranges

##### New Member
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?

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### 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:
Code:
``=AND(A2=A1,B2=B1)``
- Choose the the formatting option of white font

##### New Member
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.
Code:
``[COLOR=#333333]=IF(AND(A2=A1,B2=B1), "", B1)[/COLOR]``

Replies
3
Views
77
Replies
0
Views
210
Replies
0
Views
199
Replies
9
Views
198
Replies
2
Views
290

1,136,338
Messages
5,675,187
Members
419,553
Latest member
hanahass

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back