Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2000 Custom alphanumeric Series

Posted on 2002-07-29
6
Medium Priority
?
371 Views
Last Modified: 2012-06-27
I have an alphanumeric numbering system. I need to sort it.

It goes:
A, AA, AB, AC, AD, AE... AZ then B, BA, BB, BC, BD, BE.... BZ then C, CA, CB, CC, CD... CZ then D, DA, DB through to Z, ZA, ZB, ZC.... etc. After that it's 1, 1A, 1B, 1C.. 1Z then 1AA, 1AB.... 1ZZ, 2A, 2B.... get the drift!?

Anyway, what's the best way to create this in Excel so I can sort my revision control data. I was amazed to find that Excel didn't have a basic alphabet series in the series list. It's not a million miles away from a hexadecimal system, so it can't be that hard.
I know I can type a huge list and then import a custom series, but is there a better way? Is a custom number format a better option?

Thanks,

Steve :)
0
Comment
Question by:Steve2276
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:Elmo_
ID: 7185003
Steve,

Just one or two Questions.

1.  When you start adding numbers to the list how high do you want to go?  Are you just just 1 or want to go to something like 10 on this.

2.  Should the list not follow suit after the inital alpha list?

E.g.

You have:
A, AA, AB, AC, AD, AE... AZ
1, 1A, 1B, 1C.. 1Z then 1AA, 1AB.... 1ZZ

Should it not be:
A, AA, AB, AC, AD, AE... AZ
1, 1A, 1AA, 1AB.. 1AZ then 1B, 1BA.... 1ZZ


Cheers,

Ed.
0
 

Author Comment

by:Steve2276
ID: 7185186
My list is already pre-defined.
I don't need to create a list as such, just order an existing list.
I figured the custom series route was the correct one to take - ie. it would determine the sort order. But I'm open to suggestions.

Steve
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7185231
Steve,

Sorry about that, I took up the question wrong.  I started to think about how to create the list.

OK.  Another one or two questions:

How do you currently have the list stored?

Are you trying to define how the list should be sorted?

cheers,

Ed.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:Steve2276
ID: 7188515
The list is stored as a single column in Excel (extracted and manipulated from a text file)

Yes, I am trying to define how the list should be sorted.

Thanks.

;o)
0
 
LVL 16

Accepted Solution

by:
sebastienm earned 306 total points
ID: 7192014
It is actually far from hex or any alphanum system since
all of them are built on 'basis'. So, you could never get:
A<AB<C<CA as you series is.
Anyway...

1.
One painfull method would be to write ALL of them
in order (once) in a sheet column A, then assign row number
to column B
A 1
AB 2
...
then anywhere you need this index (col B), you could do a
Vlookup().

2.
Else, you can use the bellow VBA function in your
spreadsheet, it transforms the code into a number, then
eg: in col B, =FormatSeriesString(B1)
then order by this number. It should work.

This also assumes that i understand your series (???):
In order: (3 digit code max starting at A ending at 9ZZ)
A
AA
AB
AZ
B
BA
BZ
Z
ZA
ZZ
1
1A
1AB
1Z
1ZZ
2
2A
2AB
2AZ
2Z
9
9A
9AB
9AZ
9Z
9ZA
9ZZ
----------------------------------------------
Function FormatSeriesString(Cell As Variant) As String
    Dim s As String
    Dim s1 As String, s2 As String, s3 As String
    Dim num As Integer

    s = Cell.Value
    s1 = Left(s, 1)
    num = Len(s)
   
    Select Case num
    Case 1
        If IsNumeric(s1) Then
            s = s & "00"
        Else
            s = "0" & s & "0"
        End If
    Case 2
        If IsNumeric(s1) Then
            s = s & "0"
        Else
            s = "0" & s
        End If
    Case 3
        s = s
    Case Else
        s = "000"
    End Select
   
    s1 = Left(s, 1): s2 = Mid(s, 2, 1): s3 = Right(s, 1)
    FormatSeriesString = _
        (Asc(s1) - IIf(IsNumeric(s1), 48, 55))*(36 * 36) _
        + (Asc(s2) - IIf(IsNumeric(s2), 48, 55)) * 36 _
        + (Asc(s3) - IIf(IsNumeric(s3), 48, 55))
   
End Function
---------------------------------------------------------
Regards,
Sébastien
0
 

Author Comment

by:Steve2276
ID: 7193147
Okay, cheers. It's all looking a bit complex now. I'm not opposed to VBA stuff, and I'll give it a go. But I may have found a better field to sort by now!
Cheers.
:)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

810 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