Create ID #

Realized that I need to generate a unique ID number in excel to be able to update I would to create a unique ID number using VBA that will be used in an Access DB and not sure how to go about it.  I would like to  create a ID number by using the last three number in the storenumber+ "-" + 1 and the update will take place when the workbook is closed.  Attaching workbook with sample data.
PT-Test-06.04.xls
jmac001Asked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Yes, you are correct - there were errors
PTT-Example-2013.06.18.xlsm
0
 
als315Commented:
Look at sample. You are using 2 digit ID in your sample. In this case you can use only ID up to 99. If you can have ID > 99, you should change format of ID field.
PT-Test-06.04.xls
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

In cell [O2] you could use his formula:
=RIGHT(TRIM(A2),3)&"-"&TEXT(ROW()-1,"00")

Then copy this cell to the Windows Clipboard & paste it down the rest of column [O] to cell [O42].

However, as als315 mentioned, what happens when the data reaches row 101?

Is "316-100" a valid ID?

Are you going to restrict the data entry to rows 2 to 100 using Visual Basic for Applications code or cell protection, or even restrict use of the worksheet using the ScrollArea property?

BFN,

fp.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
jmac001Author Commented:
Als315 couple of questions:  I looked at the code and could not determine how it was determining the column just realized that I am missing 2 columns so the IDNum should populate in in Column Q.  Secondly the StoreNumber is not always going to be "316" but it will always be 8 digits long and I will always want to the last 3 digits.

There will be instances when the ID is over 100 and I did modify the the code for.

Thanks
0
 
[ fanpages ]IT Services ConsultantCommented:
Were you ignoring my suggestion because you needed further assistance, or because I didn't supply a sample workbook?

There will be instances when the ID is over 100 and I did modify the the code for.

Sorry, I do not understand what you were trying to say.
0
 
jmac001Author Commented:
Fanpages are you saying in addition to having vba to place the formula in column O?  The workbook that was provided by ALS has  VBA in it.  In response to your question there will be no restrictions as there is no limited to the number of items that can be shipped.
0
 
[ fanpages ]IT Services ConsultantCommented:
No; my proposal does not use Visual Basic for Applications; just in-cell formula.

Regarding the maximum number of items, I was asking due to the formatting of the second part of the ID (the digits after the hyphen "-").

Presently there is an assumption that there will be two trailing digits (padded to two digits; i.e. 1 to 9 will be "01" to "09").

This is achieved in the part of the formula I have placed in Bold:

=RIGHT(TRIM(A2),3)&"-"&TEXT(ROW()-1,"00")

If you wish to have three digits, right-aligned, & padded with zeros ("0"):

=RIGHT(TRIM(A2),3)&"-"&TEXT(ROW()-1,"000")

If you did not wish to have any "padding":

=RIGHT(TRIM(A2),3)&"-"&ROW()-1
or
=RIGHT(TRIM(A2),3)&"-"&TEXT(ROW()-1,"0")
0
 
als315Commented:
jmac001:
Column number is transerred to sub as a parameter:
Sub Fill_ID(ColN As Long)
I can't find store number in your workbook, so it was added manually:
StoreN = "316"
You can add it to parameters of sub and use it as:
StoreN = right(StoreNumber, 3)
If you like to use 3 digits numbering, change format to:
Cells(LastRow + 1, ColN).Value = StoreN & "-" & Format(MaxN, "000")
0
 
[ fanpages ]IT Services ConsultantCommented:
I can't find store number in your workbook, so it was added manually:
StoreN = "316"

Look in Column [A].
0
 
jmac001Author Commented:
The StoreNumber is in column A, not getting the exspected results not returning the last digits of the store number and data is still populating in column [O] and not column [Q].  Attatching the test file.
0
 
[ fanpages ]IT Services ConsultantCommented:
Do you wish to progress with a Visual Basic for Applications-based solution, jmac001?

Have you considered my proposal (further)?
0
 
jmac001Author Commented:
Fanpages although it would be easier for me to use the formula in the column it won't be easier for the end user.
0
 
[ fanpages ]IT Services ConsultantCommented:
Fanpages although it would be easier for me to use the formula in the column it won't be easier for the end user.

Thanks for finally acknowledging this.  I was beginning to think you were ignoring me on purpose.

If you wish to discuss a formula-based approach further (for example, protecting the column that contains the formula-generated Id, so that the users cannot change it), then we can do so.

Otherwise, if you are finding that the Visual Basic for Applications code you already have access to is not working as you intended, I can offer my proposal for such an approach.

That said, if a coded-implementation is rigid (as per your opening question text), then does this need to be shown to the users, or can it simply be used when updating the Access database?  Do the users need to be aware of the unique key?
0
 
als315Commented:
You can test this sample (you should have column with name ID)
PT-Test-06.04.xls
0
 
jmac001Author Commented:
Als313 the code is working in only in the worksheet you provided,  when I place it another worksheet it no longers work.  When I go to close the workbook the ID is not being populated, but will populate if I run the VBA from the editor.

Fanpages would like to explorer doing this within the worksheet.  Answer to your question the end-user does not need to be aware of the unique key,  it will be used only to create a query to append records when the worksheet is updated.

Thanks
0
 
als315Commented:
Test new sample. Now worksheet TEMPLATE is selected in sub and ID filled in this sheet.
If you like to copy code to other workbook, you should also copy call to sub to before close event for workbook:
codePT-Test-06-11.xls
0
 
jmac001Author Commented:
That is what I missing, last question, how do I take into consideration that lines will be added after the last line in the autofill?  If I test it now and the last number is 125-120 when a new line is added after that auto-fill number is 120-121 and not 125-121.
0
 
als315Commented:
I can't reproduce it.  Can you upload sample?
0
 
jmac001Author Commented:
Hi attached is the example if you look at the last three lines the number starts with 043 instead of 050.  Thanks
PTT-Example-2013.06.18.xlsm
0
 
jmac001Author Commented:
Thank you for all of your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.