Revision conversion to a number suggestions ?

I have a problem with Revisioning conversions. I wonder if any one has any 
ideas on how to do this better.  One of the elements in my database is 
drawing numbers and their Revision letter.  ie drawing GA123 Revision A . the 
next drawing would be GA123 B  and so on.  My problem is when the drawing 
rolls all the way to GA123 AA or Even 3 Letters in the revision ie Rev ABZ. 
Currently i take the asc value of each letter and add them togeter to get a 
number representing the revision.  example Rev AB would be 65+66 = 131  so 
when i compair drawings to see if a new one exists i can just look at the 
number instead of trying to compair the string letters of the revision.  the 
problem is its breaking now with this asc to number conversion. for example 
AY is 65+89=154 and Bk is 66+75=141  Bk is newier but its asc total value is 
less than the old AY rev.
thanks

0
Utf
3/30/2010 6:03:03 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
852 Views

Similar Articles

[PageSpeed] 45

Jon -

I could see where revision AK has the same value as revision KA, etc.  

Why aren't you just using revision numbers instead, like revision 1, 
revision 2, etc.?  Life would be simple.  If the user's require the A, B, C 
schema, then you could always have a lookup table for values as high as you 
want to go (e.g. 1 = A, 2 = B, 27 = AA, etc.).  Store the numeric revision in 
the database, and display the alpha value to the users when needed.

In a more general light, as far as conversions go, to prevent the problem 
you are having, you need to decide the max number of values per position, and 
design a conversion scheme around that.  If you are using only upper-case 
alpha characters (or all alphas without case sensitivity), then you can have 
up to 26 values in each position.  If you use alpha-numerics and are case 
sensitive, then you could have up to 62 values in each position.  If you want 
this to be converted to a numeric value, then each position must allow for 
all values.  Since we are normally decimal-minded, you could assign the 
right-most values to be 1-99, which would cover all the examples in this 
paragraph.  The next value would need to start after 99, so you could 
multiply the value of these by 100.  For example:

A=1
B=2
....
Z=26
AA = 1*100 + 1 = 101
AB = 1*100 + 2 = 102
AZ = 1*100 + 26 = 126
BA = 2*100 + 1 = 201
BB = 2*100 + 2 = 202
....

This way, each character value provides a unique numeric value, even though 
not all numeric values will be used.  The third character from the right 
would then need to be multiplied by 10000 to ensure unique values, etc.

-- 
Daryl S


"Jon" wrote:

> I have a problem with Revisioning conversions. I wonder if any one has any 
> ideas on how to do this better.  One of the elements in my database is 
> drawing numbers and their Revision letter.  ie drawing GA123 Revision A . the 
> next drawing would be GA123 B  and so on.  My problem is when the drawing 
> rolls all the way to GA123 AA or Even 3 Letters in the revision ie Rev ABZ. 
> Currently i take the asc value of each letter and add them togeter to get a 
> number representing the revision.  example Rev AB would be 65+66 = 131  so 
> when i compair drawings to see if a new one exists i can just look at the 
> number instead of trying to compair the string letters of the revision.  the 
> problem is its breaking now with this asc to number conversion. for example 
> AY is 65+89=154 and Bk is 66+75=141  Bk is newier but its asc total value is 
> less than the old AY rev.
> thanks
> 
0
Utf
3/30/2010 6:44:01 PM
high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD 
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price?  China 
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can 
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are 
the best brand replica goods whih are look the same as the original goods. 
excellent quality and steady supply for them. we have been marketed in Europe 
and American for 3 year. all the goods we offer are AAA quality.  our soccer 
jersey are Thailand style. If any goods you buy from my company have problem, 
we will refund or resend them again. Most of ourProducts have no minimum 
order requirements,soyou can shop retail goods at wholesale prices. if you 
can buy more than 300usd. We offer free shipping. The more you buy the more 
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping.  7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com
0
Utf
3/31/2010 8:40:04 PM
Thanks for the reply Daryl,
why would the 3rd character be multiplyed by 10,000 ?

it looks like adding a zero to each letter place holder might work




"Daryl S" wrote:

> Jon -
> 
> I could see where revision AK has the same value as revision KA, etc.  
> 
> Why aren't you just using revision numbers instead, like revision 1, 
> revision 2, etc.?  Life would be simple.  If the user's require the A, B, C 
> schema, then you could always have a lookup table for values as high as you 
> want to go (e.g. 1 = A, 2 = B, 27 = AA, etc.).  Store the numeric revision in 
> the database, and display the alpha value to the users when needed.
> 
> In a more general light, as far as conversions go, to prevent the problem 
> you are having, you need to decide the max number of values per position, and 
> design a conversion scheme around that.  If you are using only upper-case 
> alpha characters (or all alphas without case sensitivity), then you can have 
> up to 26 values in each position.  If you use alpha-numerics and are case 
> sensitive, then you could have up to 62 values in each position.  If you want 
> this to be converted to a numeric value, then each position must allow for 
> all values.  Since we are normally decimal-minded, you could assign the 
> right-most values to be 1-99, which would cover all the examples in this 
> paragraph.  The next value would need to start after 99, so you could 
> multiply the value of these by 100.  For example:
> 
> A=1
> B=2
> ...
> Z=26
> AA = 1*100 + 1 = 101
> AB = 1*100 + 2 = 102
> AZ = 1*100 + 26 = 126
> BA = 2*100 + 1 = 201
> BB = 2*100 + 2 = 202
> ...
> 
> This way, each character value provides a unique numeric value, even though 
> not all numeric values will be used.  The third character from the right 
> would then need to be multiplied by 10000 to ensure unique values, etc.
> 
> -- 
> Daryl S
> 
> 
> "Jon" wrote:
> 
> > I have a problem with Revisioning conversions. I wonder if any one has any 
> > ideas on how to do this better.  One of the elements in my database is 
> > drawing numbers and their Revision letter.  ie drawing GA123 Revision A . the 
> > next drawing would be GA123 B  and so on.  My problem is when the drawing 
> > rolls all the way to GA123 AA or Even 3 Letters in the revision ie Rev ABZ. 
> > Currently i take the asc value of each letter and add them togeter to get a 
> > number representing the revision.  example Rev AB would be 65+66 = 131  so 
> > when i compair drawings to see if a new one exists i can just look at the 
> > number instead of trying to compair the string letters of the revision.  the 
> > problem is its breaking now with this asc to number conversion. for example 
> > AY is 65+89=154 and Bk is 66+75=141  Bk is newier but its asc total value is 
> > less than the old AY rev.
> > thanks
> > 
0
Utf
4/2/2010 5:56:11 PM
Jon -

It will depend on how many combinations you can have in each position.  If 
it is 10 or less (assuming 00 is for no value), then one digit per position 
will work.  If it is 100 or less, (as in my example), then you need two 
digits per position. So the first position (e.g. first character of the 
string) would convert to two positions (00 to 99), the second position would 
take the next two digits (0000 to 9900), the third position would take the 
next two digits (000000 to 990000).  You sum the values of the three 
converted numbers to get your unique value.

ABA  converts to 010201 because the first position (left-most) A converts to 
01, the second position B converts to 0200, and the third postition A 
converts to 010000.  Add these together to get 010201.

If you only add one digit per letter, then what happens when you get to J or 
K or Z?  Your results will no longer be unique.  If you multiplied each 
successive converted value by only 10, then how would you know what 234 was?  
It could be UX (210 + 24) or WD (230 + 4) or BCD (200 + 30 + 4)?

-- 
Daryl S


"Jon" wrote:

> Thanks for the reply Daryl,
> why would the 3rd character be multiplyed by 10,000 ?
> 
> it looks like adding a zero to each letter place holder might work
> 
> 
> 
> 
> "Daryl S" wrote:
> 
> > Jon -
> > 
> > I could see where revision AK has the same value as revision KA, etc.  
> > 
> > Why aren't you just using revision numbers instead, like revision 1, 
> > revision 2, etc.?  Life would be simple.  If the user's require the A, B, C 
> > schema, then you could always have a lookup table for values as high as you 
> > want to go (e.g. 1 = A, 2 = B, 27 = AA, etc.).  Store the numeric revision in 
> > the database, and display the alpha value to the users when needed.
> > 
> > In a more general light, as far as conversions go, to prevent the problem 
> > you are having, you need to decide the max number of values per position, and 
> > design a conversion scheme around that.  If you are using only upper-case 
> > alpha characters (or all alphas without case sensitivity), then you can have 
> > up to 26 values in each position.  If you use alpha-numerics and are case 
> > sensitive, then you could have up to 62 values in each position.  If you want 
> > this to be converted to a numeric value, then each position must allow for 
> > all values.  Since we are normally decimal-minded, you could assign the 
> > right-most values to be 1-99, which would cover all the examples in this 
> > paragraph.  The next value would need to start after 99, so you could 
> > multiply the value of these by 100.  For example:
> > 
> > A=1
> > B=2
> > ...
> > Z=26
> > AA = 1*100 + 1 = 101
> > AB = 1*100 + 2 = 102
> > AZ = 1*100 + 26 = 126
> > BA = 2*100 + 1 = 201
> > BB = 2*100 + 2 = 202
> > ...
> > 
> > This way, each character value provides a unique numeric value, even though 
> > not all numeric values will be used.  The third character from the right 
> > would then need to be multiplied by 10000 to ensure unique values, etc.
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "Jon" wrote:
> > 
> > > I have a problem with Revisioning conversions. I wonder if any one has any 
> > > ideas on how to do this better.  One of the elements in my database is 
> > > drawing numbers and their Revision letter.  ie drawing GA123 Revision A . the 
> > > next drawing would be GA123 B  and so on.  My problem is when the drawing 
> > > rolls all the way to GA123 AA or Even 3 Letters in the revision ie Rev ABZ. 
> > > Currently i take the asc value of each letter and add them togeter to get a 
> > > number representing the revision.  example Rev AB would be 65+66 = 131  so 
> > > when i compair drawings to see if a new one exists i can just look at the 
> > > number instead of trying to compair the string letters of the revision.  the 
> > > problem is its breaking now with this asc to number conversion. for example 
> > > AY is 65+89=154 and Bk is 66+75=141  Bk is newier but its asc total value is 
> > > less than the old AY rev.
> > > thanks
> > > 
0
Utf
4/5/2010 2:28:01 PM
Reply:

Similar Artilces:

Inserting dashes to an existing number
Hi, I am trying to create a formula that will divide this ten digit numbe into the following dashes. Here is the number 1234000999 I need the dashes to be inserted in thi format 1234-000-999. What is the appropriate formula. Thanks for your help. Dinahros -- dinahros ----------------------------------------------------------------------- dinahrose's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1334 View this thread: http://www.excelforum.com/showthread.php?threadid=26643 Why not just custom format right click on selection>format>number>custom...

Suggestions for exponents?
Hi - I have a field that holds the "units" of a measurement as a string. It can be something like "kg" for kilograms or "m^2" for meters-squared. The folks I am working with keep asking if there is a way to display the latter with a superscript, as in m2 (with the "2") being a smaller font and higher. I know of no way to do this short of using two string fields on a form, and managing that seems kind of daunting. Has anyone dealt with this before and have any suggestions? Thanks in advance, Phil Freihofner A text box is just that! What is the...

Number formats default to dates
I have a user that every time he opens a workbook, the columns that were numbers default to dates, he changes the format back to number & saves. Then when he opens it again its back to dates - where is the setting that is defaulting the cell format to dates? Thanks -- PaulaK "PaulaK" wrote: > I have a user that every time he opens a workbook, the columns that were > numbers default to dates, he changes the format back to number & saves. Then > when he opens it again its back to dates - where is the setting that is > defaulting the cell...

Employee number and name
Is there some way that when I enter an employees number in to a cell it will return the name? Thanks for your help - Dave You need to have a table of two columns (or more for other info) where the first column is the emplyee number and the second is the name. =vlookup(A5,F3:G60,2,false) will give you the name of the empl# in A5 from the table. 2 stands for the second column of that table. -- Greetings from New Zealand Bill K "Dave S" <cadasco@SUPERhotmail.com> wrote in message news:3n1fokF1986ksU1@individual.net... > Is there some way that when I enter an employe...

Conditional Number Formats
I would like to change the number of decimals shown for each number in a row of 50 columns based upon its value. For example, if the number is >100 then no decimals; if between 100 and 10 then 1 decimal place; and if <10 then 2 decimal places. I thought I could use conditional formatting, but it appears that technique only allows one to change fonts and backgrounds, not the formats of the numbers themselves. Is there any work-around or other tool I can try? Thanks in advance. You could try the conditional formatting addin at http://xldynamic.com/source/xld.CFPlus.Download.h...

numbering items in a cell
Hi, Is it possible to number items in a cell? This number should automatically increment when I enter a new item at the end of the cell as in microsoft word. Thanks! >Is it possible to number items in a cell? If there's some kind of unique delimiter that separates the items then you can count the delimiters and add 1. For example: 1,2,3 You'd count the number of commas and add 1. So, that cells contains 3 items red yes no blue You'd count the number of space characters and add 1. So, that cell contains 4 items. You should post some examples. ...

How do I increase numbers by 10%, 15% and 20%?
This is probably very simple, but I am trying to write a formula to increase a number by 10%, 15% and 20%. For example, increasing 50000 by 10% with a result of 55000, by 15% = 57500 and 20% = 60000. With 10% in B1, 15% in C1 and 20% in D1 Enter your value 50,000 in A2 In cell B2 =$A2*(1+B$1) copy across through C2:D2 copy B2:D2 down as far as you wish Now any values entered in column A will show the corresponding uplifts under the various % headings -- Regards Roger Govier "RMPHELP" <RMPHELP@discussions.microsoft.com> wrote in message news:B1001063-AE5E-4E82-A189-3781...

Taking numbers off a website and importing them into Excel? Possible?
I have no idea if this is possible, but please help if you have a idea. I need to get some data off of a website, particularly a certain set o numbers from this website, and enter them into data in Excel. Here is an example of the website I need: http://www.covers.com/data/ncf/matchups/g8_main_1.aspx There is a table near the top of the website which lists the tw teams.... I need some way to automatically go to this website, find Syracuse, an then cross reference that with two sets of numbers, AF and AA, whic works out to be 20.0 and 25.7 I then need these numbers to be transported int...

how to make windiff pass the current line number to an editor?
Hi, I am sorry to post a windiff question in this group. I failed to find a group related to windiff and figured out you might know the answer from your daily work experience. In windiff, I place an editor command below and intend to have the gvim editor to point to the current line. Windiff translate %I to character "I" rather than the current line number as it promised. Do you know why? Please help. I appreciate it. Tony "c:\vim\vim61\gvim" +"%I" "%p" ps. "c:\vim\vim61\gvim" +100 "%p" <-- gvim will always point to ...

how do i create an automaticly generating number for a document?
I am wanting to create document with automatic document numbers, eg. invoice numbers, that will automatlicly increase with each document i create. Is this possible in excel or word? If so, can someone please explain how? You can do this with a macro in Word as described in http://word.mvps.org/FAQs/MacrosVBA/NumberDocs.htm . Brad wrote: > I am wanting to create document with automatic document numbers, eg. invoice > numbers, that will automatlicly increase with each document i create. Is this > possible in excel or word? If so, can someone please explain how? Take a look at ...

Word 2007 show combined revisions in one document
I combined several sets of revisions in one document and noticed a peculiarity: when a word was replaced without deleting the space after the word and inserting a new space, the deleted word would show up twice in the combined-revisions document -- once before the inserted word and once after. So it looked like this: (replacing "dogs" with "cats" -- I'm USING caps to show the deleted word) I like DOGScatsDOGS. Where the space had been left in, it looked correct: I like DOGS cats. There was no problem in this respect with the individual redlined do...

Add Phone Numbers to Contacts
I have a Palm TX, I do not use iTouch any more as I also have an iPod iTouch. Transferring my email contact5s to the pod was a simple sync. The problem is that outlook contacts did not contain any telephone numbers. I have those numbers in a contact file on my Palm which I can also export as vcard. Is there a way to get these addresses and phone numbers in to Outlook so I can then sync with the iTouch? ...

Page numbering problem
When I try to insert a page number in Word 2007, the position is grayed out and I cannot insert a page number. In Word 2007, I am using the following commands: Insert --> Page Number --> Bottomof Page The subsequent options are grayed out. This is a new problem that arose recently and I don't know what has changed. Exit Word and then rename the Building Blocks.dotx file in your *user profile* (there is also a copy in the Program Files folder which you shouldn't mess with). You can search for the file in Windows; be sure to search hidden files and folders. -- ...

A number of questions
Hello, I am setting CRM up for telesales and trying to make it as basic as I can I have searched the web and forums, and i have the Snyder and Steger book, but not found the answers to what i need, so hopefully someone here can help. 1. Can I have numbers next to the leads 2. Can I stop the colums sorting, when the top of the colums is clicked. 3. Apointments can be synced to outlook, but don't seem to carry the infomation from the record. It just pops up to say you have an appointment. Can this be set to pull the notes from the record across? 4. I would also like to hide the n...

Inventory Adjustment with Lot Number
How do you remove a quantity from inventory for an item that uses lot numbers. Currently the Stock Status reports shows a quantity of 167 but it should say zero. I tried doing an inventory adjustment and I had to create a new lot number. It posted through ok, but when I check the Stock Status report, the item is still there showing 167. I'm stumped. bump "Shelleyb57" wrote: > How do you remove a quantity from inventory for an item that uses lot > numbers. Currently the Stock Status reports shows a quantity of 167 but it > should say zero. I tried doing an invent...

newbee having page numbering problems
I generated a document to print ID badges, two sided. each member has page 1/2. I have 106 members = 212 pages of 1&2. makes it hard to find what I want to print. How can I change the page numbering to consecutive numbers? I found that if I move pages I can get them to number consecutively, but it is a 2 page at a time process and not exactly intuitive. Is there a way to name the pages? thx btw - i am using 2007 On Tue, 06 Nov 2007 10:11:59 -0500, bill@love.ranch wrote: >I generated a document to print ID badges, two sided. >each member has page 1/2. >I have 106 members = 21...

Bills>Estimate amount based on last X number of instances
Can someone tell me what history of instances the X refers to? The last X paid to the exact same payee? Or the last X paid with the exact same scheduled bill? I set up my credit card payment as scheduled, estimated over last 12. When I first schedule it, I have to value it manually. It does not update automatically. Later, and I'm not sure if it's the first startup of the day, or the first online update of the date, Money changes the amount to $0. The history paid to that payee is definitely not $0, but the history as paid with that bill may be, because I just rebuilt the file ...

Counting the number of times a word appears 'anywhere' on a page
I'm trying to clean up a rather large file. The first field of each row is the name of the row, and if this name does not appear anywhere else in the file, or in any of three other files, the row can be eliminated. If it appears 5 times or more, it should be marked as such. So I'm looking for a way to search for the contents of the first row in the entire page and three other pages (minus the field containing the name I'm searching for in the first place) and return the number of hits. I'm sure there is an easy way to do this, but I haven't managed to figure it out yet an...

Error number: 0x800ccc0e
Why am I getting this right after installing Outlook 2000? It was a gift. I have had no success trying to get info from the knowledge base. Nothing has changed except installing Office 2000. I configured everything just as I had previous. Going back to Outlook Express I get the same error. What is going on? Same story here. And I would love to just be able to go back to OE at this point. Darla >-----Original Message----- >Why am I getting this right after installing Outlook 2000? >It was a gift. I have had no success trying to get info >from the knowledge base. Nothing ...

Installing with new serial number
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I only use Word and Entourage, and both have gotten so buggy that they're interfering with my livelihood. If I reinstall Office using one of the other three serial numbers, will that affect any of my existing documents? And will it help get rid of the bugs? Thanks for any help you feel moved to offer this non-techie. On 3/18/09 1:00 PM, "writty@officeformac.com" <writty@officeformac.com> wrote: > I only use Word and Entourage, and both have gotten so buggy that they're > interfering with m...

Formatting numbers within combined links
I have a formula which is combining text with linked values from anothe sheet, and I want to format those numbers with bold texting and different font size so they stand out from the other text. How can do this? Jonatha -- Joniben ----------------------------------------------------------------------- Jonibenj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1704 View this thread: http://www.excelforum.com/showthread.php?threadid=39742 As a formula (vs straight text), I don't believe it can be done...at least thru v2000 (you didn't tell us what ver...

Auto
I have been working with a customer who was required to migrate from Lawson software. They were used to Lawson automatically generating batch numbers for them in Payables and Receivables and I think General Ledger as well. They think it is odd that GP doesn't create batches automatically. My basic question is anyone aware of a valid tested third party solution that would autocreate batches for at minimum Payables and Receivables? Thanks much - Adam, Its very easy to be customized, we already customized such solution for one of our customers to automatically generate batchs base...

Serial numbers in Publisher 2003
I have lost my instructions for adding serial numbers to tickets I'm printing on ticket stock in Publisher 2003. As I recall it used mail merge and an Excel spread sheet with the numbers to serialize. Can someone help me locate this instruction? Thanks. "D.Duck" <Don@ld.duck> wrote in message news:OfctWLfGJHA.3504@TK2MSFTNGP02.phx.gbl... > I have lost my instructions for adding serial numbers to tickets I'm > printing on ticket stock in Publisher 2003. As I recall it used mail > merge and an Excel spread sheet with the numbers to serialize. >...

how can I reduce number of pages in a banner
I make banners quite often for school but I do not know how I can use less pages than the one on the banner template sometimes for instance I only want a small banner covering 2 or 3 pages can anyone help explain how I can do this thanks What version Publisher are you using? If you are using 2007, File, Page Setup, Advanced. You will want to type in numbers that will not exceed the height of your stock. For example type 24" x 8.5, in print preview this will produce 3 pages. -- Mary Sauer http://msauer.mvps.org/ "corby585" <corby585@discussions.microsof...

converting "text" numbers to numbers
I have been using the technique of multiplying cells that have number entries that behave like text entries by the value of 1 to convert them to numbers. However, some mixed cell entries, convert strangely. For example the entry "6 A" (without the quotes) becomes 0.25 when multiplied by 1. Any explanation? Any ideas on how to solve other than checking cell by cell to confirm all characters are numbers? Confused! Frustated! <grin>. Bob Just figured out the "why". An entry like 6 A is converted to 6AM. Its numerical value is 0.25. Still hoping for a ...