Automated Number Assignment / Sorting Formula

Hi -

I'm looking for a formula that auto-assigns a number based on it's position in the sheet and can ideally be turned into a column formula.

Our project format has the "Project Name" as the parent row and tasks underneath as child rows. I house multiple projects within one sheet, and i need to be able to sort by the project name; however, when i sort by project name, the tasks then sort alphabetically.

I know there is a column setting to auto-assign a number to a row; however, we often have to add in new rows throughout the course of a project (for tasks that do not go at the end, but belong in the middle of a project) and the newly added rows are assigned numbers that do not correlate with the correct position on the sheet and cannot be edited.

I set up a "Sort" column that lists "1" in the parent row and has +1 in each subsequent row (each row is 1 greater than the row above it), and this can work but because we often add new rows, we will also have to remember to update the "Sort" row every time. It seems cumbersome, so just looking for a more intuitive way to sort my rows.

Anyone have a solve to this issue?

Thanks!

Find more posts tagged with

Accepted answers

KarinR

@aecross

Perhaps this helps? I tested it - seems to work! You need to Save the sheet for the AutoID to appear. The Sort ID needs to be a column formula:

All comments

Paul Newcome

Are you able to provide a screenshot for context?

aecross

Hi @Paul Newcome - here is a screenshot showing the tasks and the sort column (the sort has the +1 formula in it).

For example, in the due diligence section, i may need to insert another task between DDR AOR Review and DDR Complete; when i add in this new line (DDR PM Review), i will have to adjust the sorting on all lines as "6" repeats itself if i just drag down the (=Sort370 + 1) formula.

If I set the column to an auto number assignment column, "DDR PM Review" will be added wherever the line assignment leaves off, which would not be in the correct ascending order to sort as shown above.

KarinR

@aecross

Perhaps this helps? I tested it - seems to work! You need to Save the sheet for the AutoID to appear. The Sort ID needs to be a column formula:

aecross

@KarinR - this worked!! Thank you so much!

I am curious as to what the MATCH function actually means in this context—could you give me some insight into this?

KarinR

@aecross
Match Formula: Returns the relative position of a value in a range; first position is 1.

=MATCH(lookup value, lookup array, match type)

lookup value: The value you want to find.
lookup array: The range of cells being searched
match type:
0=exact match
1=largest value less than or equal to the lookup value (ascending order)
-1=smallest value greater than or equal to the lookup value (descending order)

In the screenshot, AutoID 10 is the 8th position in the range.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.