Formula to create helper column

Hi all! I have a column the includes names. They are not unique, so a name can appear in multiple rows. What I'd like to create is a helper column with an index starting 1,2,3,4, etc. for each name but ONLY if it is the first appearance. If the name already has an index, it should be blank. Something like this:

NAME INDEX

name1 1

name2 2

name1 [blank]

name3 3

How do I create a column formula like that? I did MATCH, which works to create a unique index for the names but it repeats the previous index. I need it to be blank instead. Thanks!

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this...

Insert an auto-number column (called "Auto") with no special formatting.

Insert a text/number column (called "Row") with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in your INDEX column you can use:

=IF(COUNTIFS(Name:Name, @cell = Name@row, Row:Row, @cell<= Row@row) = 1, COUNT(DISTINCT(COLLECT(Name:Name, Row:Row, @cell<= Row@row))))

Paul Newcome

You could reference the Auto column directly, but it may look a little funny if you end up sorting the sheet or inserting new rows into the middle. It is up to you. Using the Row method means that Name 1 will always be the at the top of your sheet, Name 2 will always be the next one down, etc. regardless of sorting/inserting/adding/deleting the rows.

All comments

Paul Newcome

Try something like this...

Insert an auto-number column (called "Auto") with no special formatting.

Insert a text/number column (called "Row") with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then in your INDEX column you can use:

=IF(COUNTIFS(Name:Name, @cell = Name@row, Row:Row, @cell<= Row@row) = 1, COUNT(DISTINCT(COLLECT(Name:Name, Row:Row, @cell<= Row@row))))

Zsolt

Wow! That works, Paul! I have 20+ years in JavaScript, C++, PHP, VBA, etc. but I couldn't crack this one. Why do I need an additional Row after the Auto? You can't use the Auto in the formula?

Paul Newcome

Zsolt

Great, thanks!

Paul Newcome

Happy to help. 👍️

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.