How to EASILY create an alpha sort list reflecting column IDs

Hi there,
The alpha column headers in calc are created internally and automatically compensate for insertion or deletion of columns so there must be some mechanism by which LO itself correlates the column number to the alpha column ID. It appears to be impossible to generate these ALPHA IDs based upon current cursor location. Column() simply returns the numeric ID from 1 to 1024. Dragging an Alpha cell simply repeats the alpha character (or sequence). It would be possible to create a Vlookup table which would supply the correlation but that still requires the manual entry of all 1024 3 digit alpha IDs, whereas the numeric sequences could be either just “dragged” or a simple +1 formula autofilled for 1024 rows.
Is there a trick I haven’t discovered yet? If no trick, can we expect the functionality to be incorporated into a future release or is it a feature request?


These posted answers may be of help:

How can I get the current cell Column letter ?

How to convert a column index to a column name?

Just for completeness:

The “names” of columns in spreadsheets and in text tables as well are numbers - just written in an uncommon way:
In spreadsheets the letters “A” through “Z” are used as digits with the values 1 through 26 weighted by powers of 26 in basically the same way as the decimal notation does it with the digits “0” through “9” and powers of 10. Specifically uncommon is that there is no zero in this system, and that therefore strings representing numbers cannot be aligned by leading zeros. As example you may check AB = 1*26 + 2 = 28 and ABC = 1*(26^2)+2*26+3=731.

For text tables the representation of column numbers is even a bit funnier: Again “A” through “Z” have the values 1 through 26, but then come “a” through" “z” as additional digits with the values 27 through 52. The systems’s base is now 52 and by powers of 52 starting with 52^0=1 at the right end the digits ar weighted. We get
AbC= 1*(52^2)+28*(52^1)+3*52^0)=2704+1456+3=4163 now - and that’s no joke. Simply make a text table with 5000 columns and check. (By the way: decimal “5000”=“ArH” in text-table-column-header-fashion.)

Joking aside: I needed the conversion in a few cases and wrote the following functions in LibreOffice Basic therefore:

Function columnIndexForColumnName(pColName As String, pMode As Boolean)          REM 1-based!
REM The columns names MUST consist of plain Latin letters. Their values as digits 
REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
REM There is NO ZERO in this system.
REM pMode=True (or<>0) tells that the column names are from TextTable, otherwise they are taken as if
REM from a Spreadsheet where only the upper case letters ("A" through "Z") are used.
If pMode Then
  radix = 52
  digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
  radix = 26
  REM To make sure an error is thrown if pColName contains an illegal character.
  REM Alternatively we might assign pColName = Ucase(pColName) for spreadsheet columns as INDIRECT() does.  
End If
lenCN = Len(pColName)
acc = 0
For j = 1 To lenCN
  d = Mid(pColName, j, 1)
  charValue = InStr(1, digits, d, 0)
  acc = acc*radix + charValue
Next j
columnIndexForColumnName = acc
End Function

Function columnNameForColumnIndex(pColNum As Long, pMode As Boolean)             REM 1-based!
REM The columns will consist of plain Latin letters. Their values as digits 
REM are the InStr positions looking up "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
REM There is NO ZERO in this system.
REM pMode=True (or<>0) tells that the column names are made for TextTable. Otherwise for Spreadsheet.
digits = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
If pMode Then
 radix = 52
 radix = 26
End If
nameStr = ""
acc     = pColNum
Do While acc>0
 dValue = (acc MOD radix)
 If dValue=0 Then dValue = radix
 acc    = Int((acc - dValue)/radix)
 nameStr = Mid(digits, dValue, 1) & nameStr
columnNameForColumnIndex = nameStr
End Function

Wow - what does the rest do? :wink:
Thanks for the tutorial and the inspiration it gave me to manipulate the 26 character A-Z alpha string with a simple repeating concatenating mid() function in calc. This was easily achieved for the first 700+ IDs with only 2 characters which I then simply copied into a [Sort List] and can now “cell drag” any alpha character to automatically generate the labels. I know I’m never going to need more than the 2 characters I generated so - Job Done.