Assign a Program Code, without using Auto Number

I am already using the Auto Number column, but I want to generate a unique [Program Code]@row for each new program added to a sheet, starting with 5,000. However, I don't want to assign a Program Code to a program added to the sheet, if the [Program Name}@row is a duplicate of an already existing program.

Program Code | Program Name

5000 | Psychology

5001 | Sociology

5002 | Theology

none | Sociology

New programs are added to the bottom of the list (when the row is copied from another sheet). No Program existed at first, so the first one (Psychology) was assigned 5000 (as a result of the formula). Then, each new program added was 500x + 1. However, when Sociology was submitted again, no Program Code is assigned because this was a duplicate submission.

I tried using the COUNTIFS to check for duplicate program names, then MAX to determine highest program code that existed. But I was getting #CIRCULAR and then #BLOCKED errors, even when I tried using a Helper column:

[H-Program Code] Helper column formula:
=COUNTIFS([Program Name]:[Program Name], [Program Name]@row)

[Program Code] column formula:

=IF([H-Program Code]@row > 1, "", IF(COUNT([Program Code]:[Program Code]) = 0, 5000, MAX([Program Code]:[Program Code]) + 1))

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

Gia Thinh

Hi @aschneiderheinze1025,

Try the solution described below:

Auto Number : A column with the auto-number property, starting at 5000.

Program Code : Column formula :

=IF([Auto Number]@row = MIN(COLLECT([Auto Number]:[Auto Number], [Program Name]:[Program Name], [Program Name]@row)), [Auto Number]@row)

Logic : If the Program Name appears for first time then Program Code will be set to the Auto Number; else it remains blank.

Hope this helps.

aschneiderheinze1025

@Gia Thinh Thanks for the suggestion, I had mentioned though that I was already using Auto Number in the sheet for something else, so I wouldn't be able to have another one in this solution to generate the 5000. Would the integrity of the numbering remain if the rows are sorted? Or if the duplicate program rows were deleted?

Gia Thinh

My solution will not be affected by row sorting or the deletion of duplicate program rows

L J

In order to see what the program code is for a program, rather than leave it blank, my inclination would be to set-up a separate master data sheet that captures all unique programs, and assigns them a code with an auto-number. Then, the Program Code can pull in via a cell reference based on the value in the Program Name field (dropdown). Just a thought for consideration.

aschneiderheinze1025

@Gia Thinh Yes, and that's great. However, can the formula be adapted so I don't lose numbers when there are duplicate Program Names. I see in your example the duplicate Sociology programs don't have a Program Code, but they have an Auto Number. So, those Auto Numbers (5003, 5004) never become Program Codes. Is there a way to still use 5003 and 5004, just skipping those rows with duplicate Program Names (i.e., Sociology 2 would actually be 5003, not 5005)?

Gia Thinh

@aschneiderheinze1025

You mentioned "New programs are added to the bottom of the list (when the row is copied from another sheet)..."

If you are using a Copy-row automation workflow in the source sheet, you can add a condition checking If the Program Name is not a duplicate" then "run the Copy-row action" . This will ensure only unique Program Names are copied to the target sheet and the Auto Number will be numbered continuously.

aschneiderheinze1025

@Gia Thinh How do you include a "check that a program name is not a duplicate" in the new sheet before copying the row from the source sheet to the new sheet? I guess I could do a cross-sheet reference formula in the source sheet to do this check, and return the result in a checkbox, so that if that box is checked in the source sheet, the automation (in the source sheet) will not do the copy.

Gia Thinh

Yes, you are correct.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.