Search code examples
excelauto-incrementuniqueidentifier

Problems with generating a unique ID in Excel worksheet

I have a worksheet - created for users data entry, where I created a unique SurveyCode (consists of a row number + file name): enter image description here

Here is the formula I used to generate SurveyCode:

      =CONCATENATE(ROW(Sheet1!A1),TRIM(LEFT(SUBSTITUTE(MID(CELL("filename",Sheet1!A1),
        FIND("[",CELL("filename",Sheet1!A1))+1,255),".xl",REPT(" ",255)),255)))

I take Row() from a separate Sheet1, because if user decides to delete one of the records in the worksheet, then one of my SurveyCode formula is broken and I will see #REF! error instead 4ClientSatisfactionSurvey, for example

and my changed formula will look as:

      =CONCATENATE(ROW(#REF!),TRIM(LEFT(SUBSTITUTE(MID(CELL("filename",Sheet1!A6),
         FIND("[",CELL("filename",Sheet1!A6))+1,255),".xl",REPT(" ",255)),255)))

with ROW(#REF!) instead of ROW(A4)

So, I decided to store Row() in a separate sheet. Even when user will decide to delete record, in that case my SurveyCode will still be kept.

But I am still having another problem - if - after record deletion prospective user will create a new record - it will not be unique, but will repeat one of the previous row numbers from my sheet1.

For example - if client would delete 4ClientSatisfactionSurvey record from the worksheet, and then will add a new record - the new SurveyCode will = 7ClientSatisfactionSurvey and not 8ClientSatisfactionSurvey

I think, my problem is in the 1st part of my formula - I am not sure how to correctly generate a unique "number" part of my SurveyCode.

Please, help...

Solution

  • You want to use a dynamic range.

    Assume your data is in Sheet1, Column A

    Click on "Name Manager" (Formulas tab of the ribbon) Click "New"

    Name: myRange Scope: Workbook Refers to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A),1)

    This creates a range that automatically changes sizes when new data is added or rows are deleted.

    You reference cells with INDEX(), so, on a separate sheet =INDEX(myRange,1) is the first cell =INDEX(myRange,ROW()) is whatever cell in the corresponding row

    CAUTION: watch your index values and use conditional statements to trap out of range errors

    WARNING: COUNTA() only counts non-blank cells, if there are empty rows then you must adjust the formula to count them

    NOTE: you can fix the range to a constant number of rows by referencing a helper cell

    Example: If 20 is entered in Sheet2!A1 and myRange refers to =OFFSET(Sheet1!$A$1,0,0,Sheet2!$A$1,1) then myRange will always be rows 1-20 regardless of inserted or deleted rows

    Having said all of that, a well designed VBA solution will be more secure and easier to use.