How to remove duplicates from 1 column but leave empty cells in place of duplicate?

Question

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.

Solution

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.

Comments

[fixer1234]

Is there a requirement that this be done in placer to the original data or is it OK to create output data that looks like you want (either an additional column and you could hide the existing column 2, or a similar set of columns in another location)?

[NickNo]

@fixer1234 Thank you for your time. My only requirement is that the filtered values are in the same row as they were originally in, so data from column 2 can be shown in column #3, #2 or any other as long as it has the duplicates removed and doesn't move vertically.

[fixer1234]

Is the data presorted so that all duplicates are always grouped together or is the requirement only that any duplicates that are together get cleaned up (e.g., "Sedan" could occur again farther down the list with other intervening values and it would just be another location to clean up any consecutive duplicates)?

[Dave]

It's nice you've added a graphic to help, but, those backgrounds represent the colours of a good quality migraine! :( Would be nicer on a plain background.

[NickNo]

The only problem is that duplicates don't have to be sequential. so a,a,a,b,a,c would still leave 4th a, though it is a duplicate.

[fixer1234]

@NickNo - I revised the formula to handle non-sequential duplicates. See if that works.

[NickNo]

No I am afraid I get the value in B1 repeated in the entire C column along with circular error. stackoverflow.com/questions/17468282/… Maybe this can help somehow.

[fixer1234]

That link isn't relevant. I suspect you have a typo. Can you paste the formulas you have in C1, C2 and C3 into a comment? It isn't clear where the B1 value would even come from.

[fixer1234]

The formula assumes your data starts in row 2 and the formula in the answer goes in C2. If you tried putting it in C1 without changing all of the cell references, it won't work. Also, you need to enter it in C2, then copy from there to have the cell references propagate correctly.