Need Formula for blank out duplicate cells based on value that is changes in another colum

[Waseem0888]

Hello

I'm looking for a formula that would blank out cells in Column B and not delete the entire row when duplicate data is found but the trick is that it needs to be based on when there is duplicate data in column A then it should just keep one value in column B and all other values related to that other column should be blank.

Column A Column B
ABC 123
ABC 123
ABC 123
DEF 456
DEF 456
DEF 456

So my result should be like this

Column A Column B
ABC 123
ABC
ABC

Thank you.

[Kaper]

Assuming your data starts with row 2 in C2 (and copied down) such formula would do the job:

Please Login or Register  to view this content.

PS. If you would like changes in column B, where the data alrteady resides, formula wouldn't help, you need to use for instance VBA code to achieve this result
PS2. Please note yellow banner above - the attachment (workbook) is often very helpful and better than text inside the post

[Pete_UK]

You could maybe use conditional formatting to "blank them out", by setting the foreground colour the same as the background colour. This would not change the values in the cells, but would effectively make them appear as blanks.

Hope this helps.

Pete

[Waseem0888]

@Kaper

Thank you for your kind help. Ok sorry about that yes i am uploadind my workbook i have tried your formula but i want it reflect in the orignal coulumn dont want to creat new column how i can do that? i have tried through conditional formating but i am not getting blank can you please help based on the values in column A the refrence number will have duplicate values and i want to apply formula to column M and column N to balnkout the dates and keep only one date.If we have to done it trough VBA than how it can be done. Your help is highly appritiated. Thankyou

[Pete_UK]

Select the cells M3:N31 so you can set the CF in one operation, then click on Conditional Formatting | New Rule | Use a formula... , and then put this formula in the dialogue box which pops up:

=COUNTIFS($A$3:$A3,$A3,M$3:M3,M3)>1

Click on the Format button, Font tab, then in the Color drop-down choose white, then OK twice to exit the dialogue box. Excel will adjust the (relative) cell references to suit the cells that had been selected, so that the duplicates will appear blanked out.

Hope this helps.

Pete