Formula to create helper column

Zsolt

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!

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.

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

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.

Zsolt

Great, thanks!

Paul Newcome

Happy to help. 👍️