unique code

[noubaba]

I needhelp to design a formula in excel to develop a unique random code, forexample I have "Received" word in A1 cell then I want to get acode in B1 cell, code should capture “R” from A1 cell then contain today’s currentdate 011019 (01 day, 10 month, 19 year) then capture any random numerical digitsto make this code unique. I have thousands of entries, code in B1 against A1must not be duplicated with all above codes in B1 column. Finally code shouldbe looked like R01101932 or R011019543 (last numeric digits 32 and 543 arerandom digits captured by formula to make each entry unique)

 

[Misca]

Something like

=LEFT(A1,1)&TEXT(TODAY(),"DDMMYY")&ROW()

should work.

The formula takes the first character of your string followed by today's date and the row number. If you replaced the ROW() with something like TEXT(ROW(),"00000") your unique IDs would look more formal.

The possible down side of using formulas for this kind of job is even your old unique IDs change every day. If you want your IDs to remain static you're going to have to use a date column (and not change your rows) or use a macro or something to replace the formulas with static values.

 

[noubaba]

Thank you for support but I need to keep my codes statics, codes must not change every day, in this given formula codes will be changed once date changed as you mentioned as well, because later on I have to link all details with these unique codes, instead of add rows I would prefer to add random numeric digits at the end of code. Please see if you or any one else can help me. Thank u.

 

[Misca]

There's no formula solution to your problem. At least the TODAY-part of the formula is going to change every day. You can solve this part by adding a date column to your data and refer to that instead of the TODAY-formula. With this addition the formula should work as long as you don't sort your rows or add / delete rows from above.

The only way to make sure your IDs wont change is to use a macro that replaces the formulas with actual values. Or have the macro generate the IDs from the scratch.

Here's a little macro I wrote to generate the ID to the selected cell:

Sub UniqueID()

Dim Time As Double
Dim ID As String


Time = Now  'Returns Date as whole number and time as the decimal part.


'Formats the date part of the time value as DDMMYY and adds the time part to the end:
ID = Format(Time, "DDMMYY") & Mid(Time, InStr(Time, Application.DecimalSeparator) + 1, Len(Time))




With ActiveCell
    .Value = Left(.Offset(, -1).Value, 1) & ID  'Takes the first character from the cell on the left and adds the generated ID-part
End With


End Sub

The code is not perfect: It expects there's a column to the left of the selected cell (and only one empty cell is selected) but doesn't check any of the expectations are met. Also, it doesn't compare the generated ID to the existing ones because it generates the "unique" part from the current time (to a fraction of a second). However, in theory there's still a change you might be able to generate the same unique time stamp twice if you really wanted to.

Now all you need to do is find out the best way to run the macro when all the expectations are met. I'd use a button but even then I'd like to use a data entry form to make sure the macro is not overwriting existing data.