I would like to know if a macro can compare two columns and remove duplicates in both columns while leaving blanks where the duplicate values were.

I found another post that helped remove duplicates from one column here and was hoping to have similar luck with my problem.

Here is an example (much smaller scale) of what I have.

and here is an example of what I am looking for.


  • What is the algorithm you are using to change the numbers in the third column between the original data and the desired results? What have you tried and where did you run into trouble? – Ron Rosenfeld Jan 26 '17 at 1:36
  • That was just an =randbetween to put data there. The data is irrelevant, sorry should have made that clearer. – JWest Jan 26 '17 at 14:47
  • If you really want to remove the duplicates, a VBA solution would be needed. But if this is just for display purposes, just use conditional formatting and format the font color to be the same as the interior (background) color. If your data is in columns A:C, the formula would be: =AND($A2=$A1,$B2=$B1) and the formula would apply to $A$2:$B$1000 (Replace the 1000 with at least as many rows as you need. – Ron Rosenfeld Jan 26 '17 at 22:30

If you are able to use a helper column you can achieve this with a quick IF() statement.

Assume: Data starting in B1 for which you wish to insert blanks for dupes.

In cell C1:


Drag/double-click this formula down along your data and only one (admittedly the last one, not the first one) will appear and all others will be a blank cell.

It's not perfect but it's quick, dirty, and easily adapted for different uses.

Edit: This kinda looks you'd be best off formatting this with a Pivot table instead of destroying your source data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.