Unique Integer Value Per Entry

Dantastic

I am trying to write a column formula that will assign an integer value to a row based on the number of times a unique value appears in a row.

SteyJ

https://community.smartsheet.com/discussion/114795/unique-integer-value-per-entry

does this work?

=COUNTIF([Serial Number]:[Serial Number], [Serial Number]@row)

Dantastic

https://community.smartsheet.com/discussion/comment/410811#Comment_410811

Hi Jacob,

This is a solution I have tried before.

I need the integer value in Instance and Cycle to match.

The function provided does not produce the desired result.

I basically need a formula that adds 1 to a count that will not alter previous entries.

Essentially Cycle should return an integer value for each separate occurrence of a serial number.

For example, the first time a serial number appears the cycle would equal 1 and the second time a serial number appears in a column the cycle would equal 2 but the cycle integer for the first occurrence of the serial number would remain as 1.

I am not sure if my description makes sense.

I have been trying to figure this out for awhile but am having a difficult time finding anything online.

Dantastic

I figured it out!

=COUNTIF([Serial Number]$1:[Serial Number]@row, [Serial Number]@row)

Thanks for the help!

I limited the range to count only from the first row to the current row.

SteyJ

https://community.smartsheet.com/discussion/comment/410842#Comment_410842

There is a cheat way, where you setup the formula I showed you above. Once you do this, you can setup automation to copy the row to a new sheet called "Countif Saver" or something like that. Have an auto number, or some kind of identifier column to INDEX/MATCH and pull cycle number back into the sheet.

This will save the number in a text box at the time it is created.

Does this make sense?

SteyJ

https://community.smartsheet.com/discussion/comment/410844#Comment_410844

awesome solution! glad you figured it out!

Dantastic

https://community.smartsheet.com/discussion/comment/410846#Comment_410846

I got the result I want but since I used [Serial Number]$1 I can't set as a column formula.

If I remove $1 and leave formula as =COUNTIF([Serial Number]:[Serial Number]@row, [Serial Number]@row) the formula is unparseable.

Maybe I can write the formula on another sheet and reference it where I need it and game the system that way.

SteyJ

https://community.smartsheet.com/discussion/comment/410848#Comment_410848

Let me know what you figure out. If all else fails, you can try the cheat way referenced above