Identify Duplicates then Add a Character (A, B, C, etc.) as a Suffix

Hello Smartsheet Community! I have searched through here a bunch but I can't quite find what I'm looking for.

I have a sheet with order #'s (Order #) and if there are multiple services to schedule, they each need a row. So my order # is duplicated. I have a column that counts if the order # is duplicated (Order # Count). I need help with the Service Order # column that will be the Order # + a suffix of A, B, or C, if it is the 1st, 2nd, or 3rd occurrence of that order # in the sheet. (It could be longer than just A, B, C).

I started with the suffix being the acronym of the service, but my client wants it to be A, B, C if it is the 1st, 2nd, 3rd, etc. I realize that changes if the sheet is re-ordered. And I can't assign based on the service type because they want the suffix to be based on the count rather than the service type. We have 15 service types and those can be duplicated within one order.

Order # is input

Order # Count is =COUNTIFS([Order #]:[Order #], [Order #]@row)

Service Order # is what I need help with

Maybe if the Order # Count could show if it is the 1st, 2nd, 3rd, etc., occurrence in the sheet, I could assign it that way? I tried INDEX(DISTINCT(...but it's the whole sheet, I also tried INDEX(DISTINCT(COLLECT(...but again it isn't giving me what I need.

Thanks!

Find more posts tagged with

Accepted answers

All comments

Lucas Rayala

Hi @ProDevWSL, if your want to create a static ID, first create a column with the Auto Number column type. This will provide a sequential unique ID to reference that won’t change if you reorder the page. Call this column RowID.

Change your formula in Order # Count to:

=COUNTIS([Order #]:[Order#], [Order#]@row, RowID:RowID, <=RowID@row)

this will give you a sequential 1,2,3,etc value for each instance of order number.

To convert the 1,2,3 to a letter is annoying. This is a bit absurd, but you can use this formula to convert numbers to letters:

=RIGHT( LEFT(“abcdefghijklmnopqrstuvwxyz”, [Order # Count]@row), 1)

This assumes you won’t have more than 26 instances of the order. If you think you’ll go over, let me know.

ProDevWSL

Thanks @Lucas Rayala! I agree, I'm not a fan of the A, B, C. I'm still trying to convince them to just do -1, -2, -3, etc. At least it gives you information without having to count which letter of the alphabet you are on! 😄

I couldn't get the alphabet conversion function to work, but I'm going to try to stick with the #'s if I can!

=RIGHT( LEFT(“abcdefghijklmnopqrstuvwxyz”, [Order # Count]@row), 1)

It keeps saying #UNPARSEABLE

Thanks!!

Lucas Rayala

Hi @ProDevWSL - I was typing this on my phone and the quotes are just the wrong type. Here's a working version:

=RIGHT(LEFT("ABCDEFGHIJKLMNOPQRSTUVWXYZ", [Order # Count]@row), 1)

Just make your service order # this formula:

=[Order #]@row+RIGHT(LEFT("ABCDEFGHIJKLMNOPQRSTUVWXYZ", [Order # Count]@row), 1)

ProDevWSL

@Lucas Rayala Oh, haha! I thought I was going crazy for a minute! I didn't realize there were different types of ". I literally changed each part of the formula except the quotations.

Ya learn something new every day!

Thanks so much for your help!!!

Lucas Rayala

@ProDevWSL, happens to everyone! I had to stare for a moment.

I thought this was handy information so I wrote a post describing how to do this generically, as well as how to expand this beyond 26 characters, going from "A - ZZ" (702 available character combinations). You can check that post out here:

https://community.smartsheet.com/discussion/116353/turn-numbers-into-letters

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.