Solved

Create ID #

Posted on 2013-06-04
20
141 Views
Last Modified: 2013-06-18
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
0
Comment
Question by:jmac001
  • 8
  • 6
  • 6
20 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39221126
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39221318
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
 

Author Comment

by:jmac001
ID: 39222425
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39222575
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
 

Author Comment

by:jmac001
ID: 39222618
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39222677
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
 
LVL 39

Expert Comment

by:als315
ID: 39223522
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39223674
I can't find store number in your workbook, so it was added manually:
StoreN = "316"

Look in Column [A].
0
 

Author Comment

by:jmac001
ID: 39223916
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39223950
Do you wish to progress with a Visual Basic for Applications-based solution, jmac001?

Have you considered my proposal (further)?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jmac001
ID: 39223965
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39224035
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
 
LVL 39

Expert Comment

by:als315
ID: 39226845
You can test this sample (you should have column with name ID)
PT-Test-06.04.xls
0
 

Author Comment

by:jmac001
ID: 39235400
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
 
LVL 39

Expert Comment

by:als315
ID: 39238964
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
 

Author Comment

by:jmac001
ID: 39245142
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
 
LVL 39

Expert Comment

by:als315
ID: 39245520
I can't reproduce it.  Can you upload sample?
0
 

Author Comment

by:jmac001
ID: 39257028
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
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39257393
Yes, you are correct - there were errors
PTT-Example-2013.06.18.xlsm
0
 

Author Closing Comment

by:jmac001
ID: 39257495
Thank you for all of your help!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now