Generate sequential number based on name

dellzy · Mar 14, 2019

Hi Excel Experts,

Am trying to find a macro that auto-generate doc ref number sequentially (at the last 3 digits on the right) based on same student's name everytime I click a button. (numbers in the middle are generated randomly).
Expected output :
Name RefNum (cell A2)
Darwin IPK-00931867-001
Maria IPK-00056091-005
Adam IPK-01968304-003
Charles IPK-02091832-002
Darwin IPK-01111532-002
Adam IPK-00099018-004
Maria IPK-00328949-006
Adam IPK-00931867-005

Please advise if I need to maintain a list to allow the macro generator can identify the repetitive name to let it be able to populate sequential numbers.

Appreciate your help.

Thank you very much in advance.

DZ

Yongle · Mar 15, 2019

Double click on cell in column B to add next ref
(conversion to proper case forces consistency)

BEFORE

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]Name[/td][td]Ref[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]Darwin[/td][td]IPK-00931867-001[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]Maria[/td][td]IPK-00056091-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]Adam[/td][td]IPK-01968304-003[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]Charles[/td][td]IPK-02091832-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]Darwin[/td][td]IPK-01111532-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]Adam[/td][td]IPK-00099018-004[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]Maria[/td][td]IPK-00328949-006[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]Adam[/td][td]IPK-00931867-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]Maria[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]Adam[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]John[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

AFTER

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]Name[/td][td]Ref[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]Darwin[/td][td]IPK-00931867-001[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]Maria[/td][td]IPK-00056091-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]Adam[/td][td]IPK-01968304-003[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]Charles[/td][td]IPK-02091832-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]Darwin[/td][td]IPK-01111532-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]Adam[/td][td]IPK-00099018-004[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]Maria[/td][td]IPK-00328949-006[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]Adam[/td][td]IPK-00931867-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]Maria[/td][td]IPK-09619532-007[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]Adam[/td][td]IPK-08626193-006[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]John[/td][td]IPK-07904800-001[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Put this in sheet module
(right click on sheet tab \ View Code \ paste code in that window \ {ALT}{F11} to go to Excel window)

Code:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Cancel = True
        Target.Offset(, -1) = WorksheetFunction.Proper(Target.Offset(, -1))
        Target = GetNextRef(Target.Offset(, -1))
    End If
End Sub

Private Function GetNextRef(cel As Range) As String
'variables
    Dim RndNo As Long, RefNo As Long, Nm As Range, maxNo As Integer, thisNo As Integer
'random no
    RndNo = Round(Rnd() * 10000000, 0)
'last 3 characters
    For Each Nm In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Not Nm.Address = cel.Address Then                                    'ignore cell we are trying to update
            If Len(Nm.Offset(, 1)) = 16 Then thisNo = Right(Nm.Offset(, 1), 3)  'validity check on other ref nos
            If Nm = cel And thisNo > maxNo Then maxNo = thisNo                  'is this number bigger?
        End If
    Next Nm
    RefNo = maxNo + 1
'put it all together
    GetNextRef = "IPK-" & Format(RndNo, "00000000") & "-" & Format(RefNo, "000")
End Function

dellzy · Mar 15, 2019

Hi Yongle,

First of all tqvm for your effort to contribute solution. I tried it out and it did populate the ref number upon double-click the cell in column B next to the name. However, the ref number seems not increase for repetitive name (I have copied the name in cell A2 and pasted into A3 and A4 to ensure each is identical input). Does it have anything to do with the Excel version am using? FYI, I'm still on Excel 2010.

Appreciate your advice.

Thank you very much in advance.

DZ

Yongle · Mar 15, 2019

Excel version is not relevant
But I do not understand your problem

- Are you saying that it does not work if the same name is on consecutive lines ?

It works for me (see below) - I added the name several times and double-clicked on each cell in turn

Or are you doing something different?

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]Name[/td][td]Ref[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]Darwin[/td][td]IPK-00931867-001[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td]Maria[/td][td]IPK-00056091-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td]Adam[/td][td]IPK-01968304-003[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]Charles[/td][td]IPK-02091832-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td]Darwin[/td][td]IPK-01111532-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]Adam[/td][td]IPK-00099018-004[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td]Maria[/td][td]IPK-00328949-006[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9

[/td][td]Adam[/td][td]IPK-00931867-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10

[/td][td]Maria[/td][td]IPK-09619532-007[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11

[/td][td]Adam[/td][td]IPK-08626193-006[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12

[/td][td]John[/td][td]IPK-07904800-001[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13

[/td][td]John[/td][td]IPK-07055475-002[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14

[/td][td]John[/td][td]IPK-05334240-003[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15

[/td][td]John[/td][td]IPK-05795186-004[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16

[/td][td]John[/td][td]IPK-02895625-005[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17

[/td][td]John[/td][td]IPK-03019480-006[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

dellzy · Mar 15, 2019

Hi Yongle,

Very sorry for the trouble. I have tested your macros on another worksheet and it did worked exactly as I wanted. Which means you understood my requirements initially. Just that I came across a problem when I have changed Name input to be in column L and starts from cell L9 downwards while the RefNum starts at column T from cell T9 downwards.

I suspect that the vba macros at the "Offset" part is affected when I changed the input and the expected output. I tried to adjust them thinking that L is column number 8 while T is column number 20 but still doesn't work, so I guess my understanding is wrong about the numbers you stated in the brackets after "Offset". I supposed I would still seek your kind help to modify for me accordingly or maybe you can explain how you define the "Offset(,-1)" and "Offset(,1)" so that I can change necessarily in future. Or maybe there are some other things I need to change?

Thanks a lot in advance.

DZ

dellzy · Mar 15, 2019

dellzy said:
Hi Yongle,

Very sorry for the trouble. I have tested your macros on another worksheet and it did worked exactly as I wanted. Which means you understood my requirements initially. Just that I came across a problem when I have changed Name input to be in column L and starts from cell L9 downwards while the RefNum starts at column T from cell T9 downwards.

I suspect that the vba macros at the "Offset" part is affected when I changed the input and the expected output. I tried to adjust them thinking that L is column number 8 while T is column number 20 but still doesn't work, so I guess my understanding is wrong about the numbers you stated in the brackets after "Offset". I supposed I would still seek your kind help to modify for me accordingly or maybe you can explain how you define the "Offset(,-1)" and "Offset(,1)" so that I can change necessarily in future. Or maybe there are some other things I need to change?

Thanks a lot in advance.

DZ

Sorry, what I meant earlier by column L is number 8 was actually at position -8 when counting from column T.

Yongle · Mar 15, 2019

OK - I will post amended code for you shortly and I will add some explanation

Yongle · Mar 15, 2019

I entered all the names first and double-clicked on each cell in column T in succession
This is what I get

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][th]

Q

[/th][th]

R

[/th][th]

S

[/th][th]

T

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td].[/td][td].[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2