Solved

Create ID #

Posted on 2013-06-04
20
174 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

738 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