Microsoft Excel 2013 – How to Remove Duplicates from One Column but Leave Empty Cells

microsoft excelmicrosoft-excel-2013worksheet-function

I have a sheet with 2 columns. first column has unique values, while the second column has many duplicates.

I would like to remove duplicates from the second column , however the cells that previously contained the duplicate should remain empty so that the first column values still matched up with the values in the second column.

Note that the duplicates are not necessarily contiguous. Only the first occurrence should be retained regardless of the subsequent location of the duplicates.

Right now when I remove duplicates the entire column compresses which is destroying the original match between the two columns.enter image description here

Best Answer

Your comment indicates that the duplicates are not necessarily sequential. Here is a formula that will handle that. Create a new display column in C and hide column B if desired. Say your data starts in row 2. Cell C2 would be:

    =IF(ISERROR(MATCH(B2,C$2:C2,0)),B2,"")

Copy that down the column. This will look to see whether the value in column B already matches an entry in column C. If so, it returns a blank. Otherwise, it uses the column B value.

Related Question