Formula to create helper column

Options

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!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!