Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Duplicate Values Conditional Formatting

Is it possible to highlight duplicate values in a column, as it is in Excel?

«1

Comments

  • JamesR
    JamesR ✭✭✭✭✭

    the only way would be if the values you are looking for are explicit and not to numerous. You could then set up conditional formatting for each of the expected values in the columns with a different formatting for each you would need to also use the add condition for each of the columns that you are testing against..

     

    So if you had a number of Rows all coloured the same then they would be duplicates.  Not tried it but it should work.

  • Brett Evans
    Brett Evans ✭✭✭✭✭✭
    edited 08/07/15

    You can put conditional formatting on a column, not a cell.   You could add a new Flag Column and flag duplicates that way.  This is the formula I used to get the functionality below: =IF(COUNTIF([Column12]:[Column12], [Column12]3) > 1, 1, 0)

    countif pic.PNG

  • Matthew Harrison
    edited 08/10/15

    Brett - thank you, this works fine.

     

    EXCEPT.

     

    It seems SmartSheet cannot cope with 5000 rows?

     

    Untitled.png

  • Travis
    Travis Employee

    Another option is to build a COUNTIF that counts the number of times the corresponding value exists in a column. You could then set up a conditional formatting rule to format cells based on the results of this formula:

     

     

    Smartsheet does have a 5,000 row limit in sheets, but it should not crash your browser to have a sheet with 5,000 rows.

     

    I looked into your account and see errors from your computer (not the app). It appears your computer is running out of memory. Chrome can be a resource hog, especially if you have a lot of tabs open and browser extensions.  I would try closing other applications on your computer and tabs in your browser to help free up memory on your computer. 

    Screen Shot 2015-08-10 at 10.12.45 AM.png

  • Matthew Harrison
    edited 08/11/15

    Thank you Travis.

     

    Update from yesterday - I removed all my conditional formatting which is now allowing me to "limp along" using IE and the COUNTIF formula suggested by Brett. I'm having to C&P the formula to only 100 cells at a time though, save, repeat, etc.

     

    I'll try and reapply the CF rules once I have dealt with the duplicates.

     

    I'm running 8GB of RAM, so this must be the main issue together with 4448 rows?

  • Travis
    Travis Employee

    Hi Matthew, 8GB is a good amount of RAM so it is strange your computer is running out of memory! I work in sheets daily with close to 5000 rows and have never had that issue (I also have 8GB of RAM). You may consider opening the Windows Task Manager the next time you see that (right click on the Task Bar and select Start Task Manager). The Processes tab allows you to sort by memory usage and can help determine which applications are taking up computer resources. 

     

    It sounds like you are using both IE and Chrome. We have found that Chrome consistently runs Smartsheet the best/fastest compared to the other web browsers. 

  • Matthew Harrison
    edited 08/13/15

    Thanks Travis.

     

    Closed all running apps, then opened SS with IE (Chrome just doesn't like this sheet - fine with all others).

     

    Memory usage below:

     

     

    All CF has been removed. It's the duplicate column that causing the issue. 

    Untitled.png

  • Chrome:

     

    Untitled.png

  • Just to test the theory, I have removed the "duplicate" column - everything now works fine and doesn't crash.

     

    (except I now cannot highlight duplicates)

  • Travis
    Travis Employee

    I tested added 5,000 COUNTIF formulas to a sheet an I am getting the same behavior. Do you have 5,000 different possible values? If you have less, you might run this formula only on the values which might be duplicated, rather than all 5,000 rows. 

  • 5000 different possible values unfortunately.

  • Travis
    Travis Employee

    Matthew - 5,000 COUNTIF formulas that are counting 5,000 cells will slow down the sheet but I did find another formula that you should try using if you are able to sort your sheet.

     

    -Sort your sheet by the column containing the duplicates

    -Add a formula to a Text/Number column that will show "Duplicate" if the corresponding cell is the same as the cell one row above: =IF(Values2 = Values1, "Duplicate")

    -Use conditional formatting to highlight the rows where the formula result is "Duplicate"

  • PERFECT!

    Thank you.

  • Is there a way to add a condition to this so that it skips empty fields?

     

    IE, if the field is empty, either:

    1) Dont flag it (empty fields are being matched with other empty fields)

    OR

    2) Dont display a flag at all in that empty field

  • Travis
    Travis Employee

    Craig, add this condition to the front of the formula to first check if a cell is blank:

     

    IF(ISBLANK(Cell1), "", 

This discussion has been closed.