none
microsoft excel suppress cell RRS feed

  • Question

  • I have a spreadsheet and want to suppress the duplicate cells.

    I use Conditional Formatting --> Highlight Cells Rules --> Duplicate Values --->  Custom Format and choose color white.

    The whole select cell range change to while. It looks like something is not right for duplicate

    It looks like that the MS Excel consider all data are duplicate, so change everything to white, but I want to keep the first one, but not white out for all cells.

    The data export from MS Access and it is date format like following:

    MyDate

    06/01/2016

    06/01/2016

    06/03/2016

    06/03/2016

    06/05/2016

    Your information and help is great appreciated,

    Regards,

    Sourises,

    Tuesday, June 14, 2016 3:18 PM

Answers

All replies

  • I use Conditional Formatting --> Highlight Cells Rules --> Duplicate Values --->  Custom Format and choose color white.

    The whole select cell range change to while. It looks like something is not right for duplicate


    06/01/2016

    06/01/2016

    That is correct, 06/01/2016 is a duplicate date, means both are colored. That is the sense of that Conditional Formatting rule, it shows you all duplicates.

    Andreas.

    Tuesday, June 14, 2016 4:23 PM
  • Thanks for the information and help,I wanted to suppress the duplicate value cell, but I want to print the first one. It looks like I am not on the right track to do this,

    Any suggestions to do suppress duplicate value cell but print the first one.

    Thanks again for your help and information,

    Regards,

    Sourises,

    Tuesday, June 14, 2016 5:13 PM
  • Hi sourises,

    Based on your description, you can try to use formula to format the data.

    Please follow these steps:

    1. Select the column- go to Conditional Formatting- New Rules:

    2. Please select Use a formula to determine which cells to format- Enter the formula and Format the Cell

    Formula: =COUNTIF($A$1:A1,A1)>1

    3. Now you will get the result as shown in the following figure:

    Hope it's helpful.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, June 15, 2016 1:52 AM
  • Hi sourises,

    here I would like to recommend you to check the suggestion given by the Emi Zhang

    I think the example given by her can help you to solve your issue.

    so please check it and let us know that your issue is solved or not.

    if you think the suggestion can helped you then I would recommend you to mark the suggestion given by Emi Zhang as an Answer.

    if not we will try to provide you further help.

    the one more thing I want to confirm with you that your approach looking like you are using an user interface to solve your issue but this forum is for development in Excel with Excel Object Model.

    so which approach you want to follow can you tell us?

    if you want to get your solution using User Interface then we will move this thread to related forum.

    if you want to get solution using code then we will try to provide you a code to solve the issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, June 15, 2016 2:28 AM
    Moderator
  • Any suggestions to do suppress duplicate value cell but print the first one.

    Sourises,

    I suggest that you use the formula suggested by Emi Zhang =COUNTIF($A$1:A1,A1)>1 into a helper column and drag it down to the end of your data. After that you can set an Autofilter and hide the duplicate rows.

    Andreas.

    Wednesday, June 15, 2016 7:04 AM