Generating Cell Refs in Excel – As Easy as A-Y

James HeslipGeek of the Week0 Comments

I’ve been working on a project for our accountant Jenny helping to generate the Networking Finance reports. In a nutshell this involves a query on the Coretime database to retrieve any relevant time entries- those of specific clients, projects and those made by appropriate employees, and also between a time-frame of one calendar month. These entries get sorted and the data is fitted with a header, detailing which clients and employees have been used for the report, which time frame has been used, and other useful information. This data is then written to Excel spreadsheet, which is where the heart of this problem arose.

To write data to an Excel spreadsheet you must first know the range of the given data. If you had a 4×5 matrix of random integers, for example, you would need to tell Excel the top left and bottom right corners of the data in Excel-speak so it would know where to place it. For our 4×5 matrix the range needed would be ‘A1:E4’. If we assume that we’re always starting at A1 then we can ignore the first half of the range. We’re then left with E4- the 4 being fairly trivial, you can see this as number of rows in the matrix. What was not so trivial to Gil and myself was taking the number of columns and determining the letter equivalent, especially as it doesn’t end at Z (going AA, etc).

Because of this repeating-letter nature, and the number of letters in the alphabet, Gil suggested that it would be possible to get a solution using encode and base 26. This made sense to me- if we treat each position (260, 261, etc.) as indices, which we can use to pick letters from their positions in the alphabet and format that string of letters to create a valid range together with the number of rows, and the initial A1 starting point.

The first stage of the problem was converting from decimal to base 26. Gil showed me a neat little trick to do this using inverse functions.

enc←base⊥⍣¯1⊢⍵ ⍝ encode in given base

Essentially by passing a single base as a left argument to decode, and running it as an inverse function using the power operator, the scalar base gets extended as many times as necessary to display the number in all its glory. This is certainly cleaner than my method of using logarithms which I’ll spare you the detail of seeing…

For my solution ⎕IO is set to 1. I will say this now to save any confusion later.

Converting straight to base 26 sounds ideal since there are 26 letters in the alphabet but when you convert the number 27 you get (1 0). You can’t use 0 as an index given the ⎕IO settings; when it rolls over it restarts at AA, which maps to (1 1). The next stage of the problem was ensuring that anything with more than one letter came out without throwing an index error. My way of tackling this was essentially by adding a one to all positions but the first.

newBase+←~(≢newBase)↑1 ⍝ Add 1 to all trailing 'bits'

What I didn’t realise at this point is that Z was not coming out in my solution. It will work for ZZ, and any other variant of Z, but not solitary Z. This is due to the way 26 rolls over to (1 0). Any range starting with a Z, e.g. ZD, would not come out previous to the following introduction:

 power←26*{⌊0.5+26⍟⍵}num⍝ Find the nearest power of 26 to the given number
 bot←power-26×≢newBase  ⍝ Subtract 26 times n where n is the number of digits in newBase
 :If 1≠≢newBase ⍝ Don't add in the first parse unless you want an index error
 :AndIf (num≥bot)∧num<power ⍝ Determine if it's in Z-range. Going to be within 26*n less than of a power of 26.
    newBase[1]+←1
:EndIf

The numbers that map to a range which starts with a Z can be determined as ones which are (26*n)-(26×n); essentially the last 26th (last starting letter). When I finally realised this, I was able to put in a check to determine whether the number was within this range. If it was, increment the first position by 1 so to get the Z starting element. By introducing Z though I seem to have pushed out those starting with Y except the initial solitary Y… It all got a little too complex for me at this point, and I think I’ll have to go back to the drawing board.

My attempt at a full “solution” can be seen below. Please bare in mind that it is incomplete at the time of me writing this and it doesn’t actually work as intended.

out←base getRange num;newBase;power;bot;⎕IO
⎕IO←1
:If num≤0
 newBase←0  ⍝ Cover as base conversion doesn't like 0
:Else
 newBase←base(⊥⍣¯1)num  ⍝ Convert to specified base
 newBase+←~(≢newBase)↑1 ⍝ Add 1 to all trailing 'bits'
 power←26*{⌊0.5+26⍟⍵}num⍝ Find the nearest power of 26 to the given number
 bot←power-26×≢newBase  ⍝ Subtract 26*n where n is the number of digits in newBase
 :If 1≠≢newBase ⍝ Don't add in the first parse unless you want an index error
 :AndIf (num≥bot)∧num<power ⍝ Determine if it's in Z-range. Going to be within 26*n less than of a power of 26.
 newBase[1]+←1
 :EndIf
:EndIf
out←(⊂newBase)⌷⎕A  ⍝ Index into the alphabet.

Gil created a far more elegant solution to retrieve the letters. This can be seen below (I put in a commented, line separated version of the shift function for readability, but essentially all that’s needed is xlbase). This could be extended by attaching the number of rows and the initial ‘A1:’ starting point to solve the full problem.

 shift←⊃{   ⍝ replace zeros by shifting from left
    0<⊃⍵:⍺,⍵   ⍝ if positive, leave unchanged
    (⍺-1),(base+⊃⍵),1↓⍵⍝ else take from left and add
 }/enc  ⍝ reduce over encoded value

xlbase←{
    ⎕IO←1
    enc26←26⊥⍣¯1⊢⍵
    shift←⊃{0<⊃⍵:⍺,⍵ ⋄ (⍺-1),(26+⊃⍵),1↓⍵}/enc26
    ⎕A[shift~0]
}

So geeks, I’ve got to ask… do you think you can find a better way?