Duplicate Values Conditional Formatting

Matthew Harrison

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

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

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)

Matthew Harrison

Brett - thank you, this works fine.

 

EXCEPT.

 

It seems SmartSheet cannot cope with 5000 rows?

 

Travis

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. 

Matthew Harrison

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

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

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. 

Matthew Harrison

Chrome:

 

Matthew Harrison

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

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. 

Matthew Harrison

5000 different possible values unfortunately.

Travis

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"

Matthew Harrison

PERFECT!

Thank you.

Craig Duffy

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

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

 

IF(ISBLANK(Cell1), "",