Unique identifier for a row

Hey guys.. does anyone have any thoughts on any way to create a formula that will create a unique identifier for a single row? I thought about using concatenate to combine a few columns in a row which will probably work 99% of the time, but I cant risk it being wrong 1% of the time. Any ideas on how to incorporate checking if the concatenation (aka the unique identifier) already exists, and if so, add a number or something to it to make it unique?

The data has rows added and removed on a normal basis in random spots on the sheet so I cant simply just have a number incrementing for each row I add as the unique identifier. In other words, the unique identifier should never change even if I add or remove rows anywhere in the sheet.

I am open to any suggestions on how to do this. Thanks.

Interesting, thanks for this. If I put that in a cell though, wont it change the unique identifier every time the sheet is calculated though?

I often take an existing row, copy it and paste it in the sheet to create a new line item, so hardcoding the cell as values would sort of defeat the purpose of having the unique identifier formula.

It still seems to recalculate when I do certain things like sorting, etc.

I thought about pasting the GUID's as values which would be fine, but I wanted to create some sort of automatic mechanism that would automatically calculate a new GUID if I copied and pasted a new row.

For example, say I take row 30, copy it, and insert it as a new row (insert copied cells). Any ideas on how to create a sheet function that can check for this behavior, go to column Z (the GUID column) on that row (now Z31), put the =GetGUID formula in that cell (to generate a unique GUID), and then copy/paste special values to hardcode it?

Any ideas?

This is just to give you some ideas on another method.
This works as long as you only copy a row and insert it BELOW the row copied, but maybe you can figure out how to work it so it works when you copy and insert above the copied row.

Use column Y to store the unique ID.
In cell Z1 put this formula (and copy it down column Z)

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Just thought of a better formula for the Z column.

Please Login or Register  to view this content.

Leave Worksheet_Change() the same

Leith Ross,

that is one useful function, thanks! I love that it generates a new number when first entered, but not when the sheet is recalculated. It is also great that it doesn't delete excel's undo-tree. I really do appreciate that knowledgeable people like yourself take the time to contribute on the net.

Anyway. I wish to write a function that works the same way, except rather than returning a "random" ID, I would like it to just return a number that is counted up each time the function is called. The counter can not be reset when the workbook is closed/reopened or computer rebooted for that matter, and the undo-tree of excel needs to be kept intact.

Any ideas how I might go about that? Or whether it is even possible?

I ended up using a simple macro that would increase a counter by one, and copy its value to the windows clipboard. Then I would just ctrl-v to the cell where I wanted a unique identifier (unique enough for my use). The advantage of this is that the actual value is kept in the cell, and thus is never recalculated (which will happen with the function approach, for example if you delete a row, etc.). I use a workbook property, not a VBA variable, to store the counter, to keep it permanently through reboots etc.

Of course, the unique identifier described in this thread has different advantages (and could be used in a similar manner with pasting values to avoid recalculation when that is an issue), and I am just describing what worked for my special case.

Some relevant links:
http://www.cpearson.com/excel/clipboard.aspx
http://www.cpearson.com/excel/docprop.aspx