Duplicate Values for specific ranges

Ghesselink

New Member
Joined
Jun 7, 2018
Messages
20
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,
 

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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
 

Ghesselink

New Member
Joined
Jun 7, 2018
Messages
20
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,249
Office Version
  1. 365
Platform
  1. Windows
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]
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top