Formula to auto increment unique IDs

Mike Tomei

Referencing the example image below, I'm looking for a formula that will automatically create incrementally numbered unique IDs, like what's shown in the column "Unique device ID within each room". That column's ID needs to be unique to the room number, but the ID is repeated in other room numbers, as shown by COMPUTER1 appearing in two different rooms. I'm having a hard time wrapping my head around a formula that will automatically populate that "Unique device ID" column, but will also allow us to insert rows for each existing room and automatically create incremental unique IDs as inventory is added. I've seen this done with auto numbering columns, but that doesn't seem to work when rows are added. Thanks.

Courtney S.

I'm coming from Excel with my solution, and I can't figure out how to make it work without having a "dummy" row at the start (in Excel the "dummy" row would be the headers). So, it can't be turned into a column formula as it's currently written. If you want to try to format this so that you can maybe turn it into a column formula, maybe this will help: @row referencing cell above — Smartsheet Community

Basically, there is a formula in a new "Device Suffix" column that I added that provides the room-specific device ID number suffix, restarting numbering by checking if the room and device prefix in the row both match the above row, If they don't both match, then it starts again at 1. Otherwise, if they both match the row above, it just increases the above row's suffix number by 1. Then in your device ID column you'll want to combine together the prefix and suffix columns to make the ID.

I began the "Device suffix" formula in row 2, and then dragged it down. I assume you would need to manually drag down the formula too. The formula in that row 2 of "Device suffix" is: =IF(AND(Room@row = Room1, [Device prefix]@row = [Device prefix]1), [Device suffix]1 + 1, 1)