Numbers better as Text?

[Tom]

Hi,

I use random numbers as Customer ID's. Is it better to save these in numeric or text format?

I am nervous that any sort as a number will throw the ID's out of whack!

Or am I just paranoid?

[GS]

Tom was thinking very hard :

Normally, CustomerIDs are text values regardless of how they're
created/configured. Either way, sorting shouldn't upset things any as
this is usually a binary-based process meaning the number 1 is still
number 1 regardless of what data type it is formatted as (AFAIK)!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[Jim Cone]

Adding to Garry's comments...
Select the data manually before sorting, don't rely on Excel to auto select the data.
Also removing blank columns/rows from your data helps prevent partial data selection.

In addition, numbers do Not sort in numerical order when mixed with alpha characters...
"A10" sorts before "A9"
'----
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
Special Sort Excel add-in (30 ways to sort)




"GS" <g...@somewhere.net>
wrote in message
news:jm06sf$vsm$1...@dont-email.me...

[GS]

on 4/9/2012, Jim Cone supposed :

> In addition, numbers do Not sort in numerical order when mixed with alpha
> characters...
> "A10" sorts before "A9"

Good point! I normally would use "A00:A09" so the sort happens
properly. This is normally due to IDs being 'fixed length' fields and
so if it was 10 characters then the format would be
"A000000000:A000000009" followed by "A000000010". However, random
length is also common but does present some issues when sorting...

[Tom]

I had previously posted on how to assign random #'s as ID's and it was suggested that I use #'s say from 400-800 in one column and in the next column use -RAND() and then sorting them to insure random of the first column.

I can easily add an Alpha character =& "XX, is that a good suggestion, or should I leave things as is?

Thanks

[GS]

Tom formulated on Tuesday :

Well.., it's not uncommon to use a fixed length abbreviation of the
customer name and append numbers to it so as to avoid duplication. I'm
not sure RAND() is the way to go, though. I might use an incremental
methodology similar to what I use for record indexes, where the alpha
part is a fixed length and the number part is a fixed length. In this
case the formula would operate on the number part to increment it for
each additional record added to the table. For example...

"ABCD000001" (1st record)
"ABCE000002" (2nd record)
...and so on

..where the alpha part refs the abbreviated customer name and the
number part assigns it a digital ID. That allows for either customer
abbreviation to repeat as appropriate without causing an ID conflict...

"ABCD000012" is distinctly different than "ABCD000001"

HTH

[GS]

Tom expressed precisely :
> What would you use for a formula?

If you provide an address I can email you a sample workbook that
contains a formula-driven autonumbering example for creating sequential
part numbers based on an alpha series identifier. Each new item added
to the series gets its ItemID auto-assigned based on a 'next-in-series'
concept.

ColA is a helper that's hidden by default, and the entire example makes
extensive use of local scope defined names.

One note of caution: if you delete an item block the remaining item
blocks below it will renumber themselves to persist sequential
numbering. If you use this methodology you can't delete IDs once
they've been assigned and used elsewhere. In this case, obsolete items
are left intact and the ItemID can be formatted with StrikeThrough to
indicate the item is discontinued.

In your case the ItemIDs will be CustomerIDs, but it doesn't support
variable SeriesIDs. That means all your IDs need to have the same alpha
text. (A different scheme needs to be devised that will allow you to
mix alpha parts of the IDs)