# Formula help

Can someone help me with a formula that would produce the hand keyed results in "Unique ID"?

I want to produce a unique ID in sequential order for each record that "Job #" and "Part Mark" are duplicated.

I've tried several Countif statements with no luck.

=COUNTIFS([Part Mark]@row, [Part Mark]1, [Job #]@row, [Job #]1) worked but would not continue counting past 1.

• ✭✭✭✭✭✭

You will need to insert a system generated auto-number column (called "Auto" in this example) and then use a formula such as this:

=[Part Mark]@row + IF(COUNTIFS([Part Mark]:[Part Mark], @cell = [Part Mark]@row, [Job #]:[Job #], @cell = [Job #]@row, Auto:Auto, @cell<= Auto@row)> 1, "-" + COUNTIFS([Part Mark]:[Part Mark], @cell = [Part Mark]@row, [Job #]:[Job #], @cell = [Job #]@row, Auto:Auto, @cell<= Auto@row), "")

• ✭✭✭✭✭✭

You will need to insert a system generated auto-number column (called "Auto" in this example) and then use a formula such as this:

=[Part Mark]@row + IF(COUNTIFS([Part Mark]:[Part Mark], @cell = [Part Mark]@row, [Job #]:[Job #], @cell = [Job #]@row, Auto:Auto, @cell<= Auto@row)> 1, "-" + COUNTIFS([Part Mark]:[Part Mark], @cell = [Part Mark]@row, [Job #]:[Job #], @cell = [Job #]@row, Auto:Auto, @cell<= Auto@row), "")

• Works like a charm.

• ✭✭✭✭✭✭
• @Paul Newcome Maybe you could assist me with another question? I'm gathering this information by having employees fill out a form. Is there a way for the "unique ID" that we generate to show up on the form confirmation screen? I'm trying to avoid employees having to go back to the sheet and refresh in order to get that information.

• ✭✭✭✭✭✭

I hope you're well and safe!

Unfortunately, it's not possible now, but it's an excellent idea!

Here's a possible workaround or workarounds

• Send an automated alert with the ID after submission.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

@Tschenk91 It is not possible to have it populate on the form confirmation, but you can set up an Automation to send an alert to the submitter that includes that number.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!