Forum Discussion
Solved
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, but sometimes the column data cannot be combined in a unique way.
How can I do this? I tried a GUID, which is great (unique), but not very user friendly (easy to reference) and it recalculates automatically. The sheet needs to automatically recalculate (I have xlookups).
One basic solution I thought of, is use the GUID formula to generate the new IDs, then just paste over the cells with values only. Is there not a more elegant way of achieving my objective?
Thanks for the support!
- 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.
- 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)
