Generating unique numbers while filling forms

[simplicityq88]

Dear friends - excel professionals and excel learners,

I've stumbled up a problem in one of my tasks. Let me try to explain what I actually need.

Well like in databases when you enter a new field the set unique identifier put a random number, the same thing I'm trying to reflect here on my excel project.

I would like to imediatelly say that RAND functions can't be of much help since they generate new ones every time, I need a number that will generate and stay there all the time.

Example:
................... 9 digit ....................... 15 digit ..................... 8 digit
Name________Number 1____________Number 2_____________Number 3

Test _________123456789__________ 1.....15 ______________87654321
___________________________________________________

WHEN I TYPE IN THE NEW NAME so: IF Ax IS NOT BLANK GENERATE UNIQUE NUMBER for Every of these digits.

I've found some scripts that check if the next entered numbers are unique and gives a checkbox but I have not found a solution like this one.

Thank you friends in advanced!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range, n As Long
Application.EnableEvents = False
If Not Intersect(Target, Columns("Q:Q")) Is Nothing Then
Set Rng = Range(Cells(1, Target.Column), Cells(Rows.Count & Target.Column).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, ""
Else
MsgBox "The value " & Dn.Value & " Is a Duplicate"
Dn.Value = ""
End If
Next
End With
End If
Application.EnableEvents = True
End Sub

 

[al_b_cnu]

Hi,

could you base it on the current row / column, e.g.
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">15</td><td style="font-weight: bold;text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Alpha</td><td style="text-align: center;;">Number 1</td><td style="text-align: center;;">Number 2</td><td style="text-align: center;;">Number 3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Beta</td><td style="text-align: center;;">323232323</td><td style="text-align: center;;">333333333333333</td><td style="text-align: center;;">34343434</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Gamma</td><td style="text-align: center;;">424242424</td><td style="text-align: center;;">434343434343434</td><td style="text-align: center;;">44444444</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=LEFT(<font color="Blue">REPT(<font color="Red">ROW(<font color="Green"></font>)&COLUMN(<font color="Green"></font>),B$1</font>),B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=LEFT(<font color="Blue">REPT(<font color="Red">ROW(<font color="Green"></font>)&COLUMN(<font color="Green"></font>),C$1</font>),C$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=LEFT(<font color="Blue">REPT(<font color="Red">ROW(<font color="Green"></font>)&COLUMN(<font color="Green"></font>),D$1</font>),D$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=LEFT(<font color="Blue">REPT(<font color="Red">ROW(<font color="Green"></font>)&COLUMN(<font color="Green"></font>),B$1</font>),B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=LEFT(<font color="Blue">REPT(<font color="Red">ROW(<font color="Green"></font>)&COLUMN(<font color="Green"></font>),C$1</font>),C$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=LEFT(<font color="Blue">REPT(<font color="Red">ROW(<font color="Green"></font>)&COLUMN(<font color="Green"></font>),D$1</font>),D$1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[simplicityq88]

Hi Alan,

Well I've tried to implement that formula in my sheet and shows me an error, then I've tried to copy the exact structure of your sample and it still doesn't work.

Best regards,
Jasmin


EDIT:

Used semicolumns instead of ,

=LEFT(REPT(ROW()&COLUMN();B$1);B$1)

Works, let me implement it in my project and I'll get back to you.

 

[al_b_cnu]

Hi Jasmin,

Well I've cheated a bit for the example - row 1 contains the length of the number you require, so the formulae column B, for example, could alternatively be written as:

=Left(Rept(Row()&Column(),9),9)

and the formulae in Column C could be written as:

=Left(Rept(Row()&Column(),15),15)

EDIT:
Just spotted your edit

 

[simplicityq88]

Glory Glory ManU. I hope your not a city fan...

Well I've edited the formula to suit my needs but belive me or not they are actually not all UNIQUE

=IF(XX="";"";LEFT(REPT(ROW()&COLUMN();9);9))

For ~3000 values I had 11 duplicates...

And is it possible, by this way, to have it lets say start with 1xxxxxxx or 2xxxx, or 3xxxx but never start with any other number than those 3.

And is it posible to mix up letters? Lets say I have to have 6 digits and 2 letters randomly.

I still think that this is going to have to be a VBA job and not simply excel formulas but thank you for the great deal of help!

 

[al_b_cnu]

Hi,

How about this:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">15</td><td style="font-weight: bold;text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;text-align: center;;">Number 1</td><td style="font-weight: bold;text-align: center;;">Number 2</td><td style="font-weight: bold;text-align: center;;">Number 3</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Alpha</td><td style="text-align: center;;">1015171GJ</td><td style="text-align: center;;">1000000085483AA</td><td style="text-align: center;;">155796VQ</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Beta</td><td style="text-align: center;;">3578769RS</td><td style="text-align: center;;">3000000649082LI</td><td style="text-align: center;;">319394GZ</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Gamma</td><td style="text-align: center;;">2165256MP</td><td style="text-align: center;;">2000000200412JY</td><td style="text-align: center;;">235569GG</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">C$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">D$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">C$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">D$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">C$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=GetUniqueValue(<font color="Blue">D$1</font>)</td></tr></tbody></table></td></tr></table><br />

Option Explicit

Function GetUniqueValue(ByVal Length As Integer) As Variant
Dim iColumn As Integer
Dim lRow As Long
Const sChars As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim sResult As String

'-- Get caller row & column --
With ActiveSheet.Range(Application.Caller.Address)
    lRow = .Row
    iColumn = .Column
End With

'-- Initialise random Number generator with seed --
Rnd -1
Randomize (lRow * 1000) + iColumn

sResult = Int((3 * Rnd) + 1)
sResult = sResult & Right$("000000000000000" & Int((1000000 * Rnd) + 1), Length - 3)
sResult = sResult & Mid$(sChars, Int((Len(sChars) * Rnd) + 1), 1) & _
                    Mid$(sChars, Int((Len(sChars) * Rnd) + 1), 1)

GetUniqueValue = sResult

End Function

No, not a City fan BTW ;¬)

 

[simplicityq88]

Thank you for the time Alan,

I've been through the formula and I get the point, the only problem is it isn't working at my 2010 Excel. I get the #Name! error - part of formula not recognized but knowing a little bit of programing I've got the formulas meaning.

I could make 3 formulas for each of the numbers so I don't need to pull it from a cell, can't I? Also if I was to mix up some of the values maybe not put letters somewhere or put more somewhere I can edit it thanks to the formula you provided.

Best regards Alan and TY and I'll get the formula working somehow.

 

[al_b_cnu]

Hi Jasmin,

Do you mean the 'getUniqueValue' formula? - if so, I suspect that you've either not enabled macros, or not put it into a module (from VBA menu Insert / Module)

Yes, you can, of course, quote a number instead of a cell reference if that fits in better with your s/sheet.

That said, as you can see I tested on 2003, however no reason why it shouldnt work in 2010

Best wishes

Alan

 

[simplicityq88]

Yes the module was the issue, I've rushed it and put it as a part of the code.

Ok now it works well, thank you very much. I'll play around with it to suit my needs and if something is out of my range I'll contact you.

Thank you again and best regards and may United win the PL, CL :D

 

[al_b_cnu]

Hi Jasmin,

By all means post back if problems.

Alan

OK, I know I live in Manchester, but I'm not actually a football fan BTW - I prefer running.