Numbering Duplicates Based on a Date

Please see above a sheet for quote submissions, I want the revision number to automatically number the duplicates of service and contractor based on submission date with the oldest being revision number 1.

Currently the Revision Number formula is:

=IF(ISBLANK([Service + Contractor]@row), "", MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], [Service + Contractor]:[Service + Contractor], [Service + Contractor]@row), 0))

Where the Row ID is just an auto number column. Currently the revision number works if newer submissions come in at the top of the sheet, but I want a formula to ensure no matter the position on the sheet the revision number will be ordered by date.

Any suggestions would be greatly appreciated, thank you!

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

Hollie Green

=COUNTIFS([Service + Contractor]:[Service + Contractor], [Service + Contractor]@row, [Submission Date]:[Submission Date], <=[Submission Date]@row)

All comments

Hollie Green

=COUNTIFS([Service + Contractor]:[Service + Contractor], [Service + Contractor]@row, [Submission Date]:[Submission Date], <=[Submission Date]@row)

LindsayD

Thank you so much Hollie, I was definitely overcomplicating it in my head. Great simple solution!

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.