Formula help

Tschenk91

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.

Thanks in advance

Paul Newcome

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), "")

Paul Newcome

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), "")

Tschenk91

Works like a charm.

Thank you for your help!

Paul Newcome

Happy to help. 👍️

Tschenk91

@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.

Thanks in advance

Andrée Starå

Hi @Tschenk91

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!

Paul Newcome

@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.