?
Solved

Create ID #

Posted on 2013-06-04
20
Medium Priority
?
183 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 40

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 40

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
 

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 40

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 40

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 40

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 40

Accepted Solution

by:
als315 earned 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 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