countif: Each project has an ID, but i need to see a warning when we put a same ID number or text

sistecompanama74176

I need to compare on ID cell values.  Each project have an ID, but i need to see a warning when we put a same ID number or text, then we need to fix this new row below to the orginal project row, thanks for your help

johann

Andrée Starå

Hi Johann,

It could probably be set up with conditional formatting or an alert/update request.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

You could use a helper checkbox column with the formula of

 

=IF(COUNTIFS([ID Number Column]:[ID Number Column], @cell = [ID Number Column]@row) > 1, 1)

 

This will check the box if there is a duplicate. You can then set up some conditional formatting to make those rows stand out that have the box checked so corrections can be made.

sistecompanama74176

thank you very much, so now i need to copy this formula to the rest of the rows. I mean while I put info into cells, that the formula copy automatically.  If posible that??

thanks again

 

Andrée Starå

Yes, if there are rows before or after it should continue the pattern, meaning it will add the formula to the cells.

More info: https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Hope that helps!

Best,

Andrée

Paul Newcome

Happy to help. 

sistecompanama74176

Hi, now i need that the formula ignore the blank space. can you help me?? thanks

Genevieve P.

Hi @sistecompanama74176

It looks like you posted a new question asking how to adjust the formula to ignore blanks and I've answered you here: https://community.smartsheet.com/discussion/67767/i-want-to-know-when-i-add-a-duplicate-a-same-value-on-a-column-how-can-i-do-that#latest

Christina Reid

@Andrée Starå - you mentioned that this might be accomplished by conditional formatting. I really do not want to have to add a helper column as I've already had to do that to many times to account for ways SmartSheet does not mimic Excel (which I understand is not really the intention, but the excel features are helpful). But in any case, my spreadsheet is now much larger due to the help columns.

I have an account number column. As a new row is entered I'd like to have the cell change colors if the account number matches a previously entered account number.

I don't think this is possible after all my searching and testing but you've seen to solve all my other issues so far so I thought Id'd take a shot!

Andrée Starå

@Christina Reid

Unfortunately, you're right. It's not possible with helper columns. There's more room for them now that we can have up to 400 columns 😉

What I usually do in my client solutions is that I add all helpers (where possible) to the far right of the sheet.