Forum Discussion
STATIC unique identifier for each Excel row
Hello, I need to generate a unique ID for each new row into the sheet. It must be static, therefore it cannot recalculate, or change if a row is added/removed. I have thousands of rows of data, b...
- so I know this is old, but I have been watching it to see if or what solution might be proposed.
I am also interested in know what is GUID (I know what the acronym is and use it quality/regulatory/FDA/EUDAMED/etc... but you mention it like it is an Excel function which I do NOT know about). Is it part of an add-in/tool box you are using?
As for an answer to your question, you should be able to use a CONCAT of some or all the columns to create a unique identifier and if it isn't unique then you have 2 identical rows so does it matter that the ID isn't unique since they are identical any how? Alternatively it could be done using VBA/macro, but I try to avoid that. And as you mentioned you can create a unique list of ID#s (easiest is SEQUENCE(N) and then copy and paste-values.
mtarler Hi, thanks for replying. Yes it seems no better solution exists, or I guess it would have been proposed by now 😄
To generate a simplified GUID, I use =DEC2HEX(RANDBETWEEN(0,4294967295),8). Its complex enough for my needs, and then I paste-values to ensure the ID never changes.
My problem is tricky, as no column has simple data, which is unique. It is a Hazard Log, so often there are cells with text, or MATCH values. I have cells which I use to trace back to the source of the data, but that would not be user-friendly in a CONCAT. A simplified GUID might not be easy to remember/share, but it feels more elegant! Thanks for the tip on using SEQUENCE, its not one I thought about!
I think a couple of key improvements excel could introduce would be to have tables include:
a) an 'entry' line above it that once entered would be 'inserted' into the table (i.e. so you don't have to scroll to bottom of table to add next entry
b) then include a (optional) timestamp field with the above (i.e. auto enters date-time when that entry is entered)
c) and include a (optional) UID field with the entry (although time-stamp above would work in most cases, if you copy and paste a number of lines into the table that time-stamp may show identical (at least to excel precision) values)
a) an 'entry' line above it that once entered would be 'inserted' into the table (i.e. so you don't have to scroll to bottom of table to add next entry
b) then include a (optional) timestamp field with the above (i.e. auto enters date-time when that entry is entered)
c) and include a (optional) UID field with the entry (although time-stamp above would work in most cases, if you copy and paste a number of lines into the table that time-stamp may show identical (at least to excel precision) values)
