Saturday, 1 June 2019

Sudoku: To automate ... or not to automate

In this article, I consider some of the options available to a spreadsheet designer for the automation of the interactions between the user (the Sudoku player) and the software he is using to record his moves.

The level of sophistication of Sudoku players varies hugely. I first discussed the process and the methodologies used to govern the interactions in a paper Spatial Modelling Techniques in Microsoft Excel. This paper considered the beginner level interactions with the puzzle.

The jump between the detail needed for beginners and that intermediate users is a major one. The design route we choose to take has significance both for the influence the player has both on the route he takes to solve the puzzle and the level to detail he has to contribute. The overall approach remains to demonstrate
  • the step wise route that any individual used to solve the puzzle, and
  • verify by analysis that those steps were valid.

The decisions on how to automate and the level of sophistication of that automation process derive from experience - not always happy experience.

A Manual Approach

A curiosity about Sudoku puzzles is that essentially the same analytical process can nearly always be considered from two or more opposing perspectives. This is like viewing a mountain from its base or from its summit or from another nearby mountain. For example one can examine a row of cells and for each cell ask the question is there any single cell for which only one Identity is permissible because all others have been eliminated for that cell. Alternatively, you can consider the Identity group and ask the question is there any single Identity where only one cell within the row is permissible. Of course, it is necessary to ask the latter question in respect of each column and each sub-grid. It is this latter approach which generated my first detailed analysis of the Sudoku puzzle grid.

The spreadsheet layout is shown below. It demonstrates a case the player identified a solution for Id 2 in one cell; in this case Row 6 Column 9 (R6C9). However to complete the task, the user/player is required to consider and, if necessary, enter a value into each of the cells with a deep emerald green background. 



He is invited to review each Id (9) from each dimension perspective (3) and in respective each position in the Sudoku puzzle (81) - potentially there are 9 x 3 x 81 = 2,187 cells to complete.

Unfortunately, functional though this approach is, it does not deliver a verifiable step in the solution to the Sudoku puzzle. In the context of the spreadsheet model as compiled at that time, a further step was required. This is demonstrated below.


As the heading within the diagram implies, Sub-grid exclusion eliminates those cells where Id 2 already exists within the sub-grid. Whereas Columnar exclusion eliminates those single cells within the row where the Id 2 is already fixed for the column. The final row in this diagram states that only one position is permissible for Id 2 and, of course, that position is R6C9.

At this point, I confess, my patience gave way and I begin to wonder whether my readers will even have got this far.

Raw data and its potential uses

I was guilty of two failures. The review process described did not deliver a usable result for Sudoku player and also the player had to repeat essentially the same process for each of the three dimensions. 

The revised process shown below eliminates the vertical and sub-grid dimensions. The horizontal dimension is much easier to follow in the user's imagination. This also excludes all cells where the result is known. These cells are marked automatically as 'Fix'd'. In addition if the Id for a row is known, the whole row is excluded from the analysis. The user is invited to review each Id in turn and manually exclude all cells where the presence of the Id in either the vertical or sub-grid dimension excludes its availability.


Finally, this analysis delivers a usable result. Instead of the 9 x 9 grid shown as "Grid Current Status" in the picture above, we have a 9 x 9 x 9 grid as shown below.


It shows all the essential detail about the state of the puzzle. Curiously, it does not answer the question proposed at the beginning of this blog. If it had the four Id's circled in red would have been presented as the solutions to their respective cells and presented with a green background.

While this detailed grid is a useful result, it still involves a substantial amount of work. Since the only user was likely to be me I set about automating the process still further.

Full automation

The automatic process are described in the blog Sudoku: Options for layout and analysis in a Spreadsheet. When the process is applied to to this Sudoku puzzle we get an output in the form shown below.


Careful examination shows the two outputs to be almost identical. The only change that I can see is almost certain to be a user error perpetrated by me.

Viewing a puzzle in Excel

These Sudoku puzzles are worked in within an excel workbook. This particular example may be downloaded at Observer Sudoku 20190525.

Sunday, 26 May 2019

Sudoku: Options for layout and analysis in a Spreadsheet

To state the bleedin' obvious, a spreadsheet is a a two dimensional array of addressed cells. When working with an environment that has 3 or more factors that require analysis its presentation in terms of layout and organisation are of crucial importance.

A Sudoku is unusual in that it presents 3 dimensional factors (row, columns and sub-grids) in a 2 dimensional surface. A further confusion is that Sudoku uses that values 1 - 9, but these are not numbers and you cannot employ them in simple arithmetic. A pervasive view is that spreadsheets are exclusively for arithmetic purposes (like monetary accounting). I have always railed against that view and here I consider that options for modelling the solution to a Sudoku puzzle in a spreadsheet environment.

The Sudoku model and its Purpose

For many, the only purpose of an automated (or semi-automated) approach is to find a solution quickly. But you can do that by instinct and guesswork far more quickly than building a spreadsheet model. I thought that my model should demonstrate

  • the step wise route that any individual used to solve the puzzle, and
  • verify by analysis that those steps were valid.
There has been a long running debate among spreadsheet users and constructors about how much of the sheet should be visible when in every day use. This discussion is concerned with the integrity of spreadsheets in a commercial or administrative environment. Sudoku is a game and everything should be visible. My one concession to security is that I protect the individual worksheets in the process to prevent accidental changes to model verification elements, but they can easily be unprotected. There are no passwords.

How much detail?

This really depends on the sophistication of the analytical methods that any individual intends to employ.  Since it does not take long for enthusiastic players to get moderately sophisticated, I chose to include sufficient detail within the model to handle that. 

The puzzle for solution is normally presented as a 9 x 9 grid. For example


Most experts on Sudoku present the detail of a puzzle in the format below. There various forms of the method of presentation but they all have the same fundamental format of a large 3 x 3 grid each containing a medium 3 x 3 (the basic Sudoku cells) and each cell contains a 3 x 3 grid of the potential Identities. For presentation purposes, solutions are presented as large Identities that overwrite the lowest level of potential Id's.


In theory, I could mimic this in Excel, but it was clear that the verification analysis would become very difficult and muddled. The choice of presenting the available Sudoku Id's for each cell in the seemed both convenient for presentation and for the analytical verification that would be required.

Even so a presentation that potentially contained all the detail of the puzzle, was likely to be difficult to understand at first glance (see below). A visual key to the Sudoku cells would be a huge advantage.


I would to make use of the presentational aspects of Excel that were inherent within a standard package. This suggested that an approach as shown below would be both functional and quickly understood.


The fist level of detail need to generate the presentation is discussed at A Game of Control and Error Management

Saturday, 25 May 2019

Sudoku:A Game of Control and Error Management


The game of Sudoku and the methodologies for its solution have been  analysed by widely academics and other experts (for example The New Sudoku Players' Forum). The purpose of this article is to consider how we can demonstrate the relationship between the elements of the Sudoku once a particular Identity has gained control of an individual cell.

The modelling environment for this discussion is an Excel spreadsheet. An incidental consideration is the notion that spreadsheets have a wider modelling capacity than number crunching or simple database management.

Introduction - Names and Lists

The puzzled is built in a 9 x 9 grid of cells, but, from the perspective of a spreadsheet analyst, this is a very difficult structure to handle. We like simple lists. Also all Sudoku experts use a row/column nomenclature to describe their methodologies and solutions.

Conventional cell names for a Sudoku puzzle.


Our list is in the form of single column starting with the names of all the cells in row one and proceeding through each row. This gives a complete list of each of the 81 named cell.



Next we create a table, associated with the list of named controlling cells, of all the cells related to the controlling cells.


The cells which are logically associated with each 'master' cell. 
For example, if cell Id 42 contains the result 2 then it follows, by the rules of Sudoku, that a list of 20 further cells may NOT contain the result 2. These are:


  • In row 4 - cells 41, 43, 44, 45, 46, 47, 48 and 49
  • In column 2 - cells 12, 22, 32, 52, 62, 72. 82 and 92
  • and in the 4th sub-grid - cells 41, 51, 43 and 53


This analysis is repeated for each of the controlling cells within the list of 81 named list. The final table comprises 81 rows and 20 columns (excluding the name column).

Now we know the relationships between controlling cells and others in the Sudoku table, but the analysis depends both on whether the controlling cell contains a result AND the Sudoku identity of the result. In effect each Sudoku identity must be analyzed and computed separately.

The Analytical Process


The process for evaluating whether an individual Sudoku Id is still available as a possible solution to a cell is long winded. Each cell containing a result has been evaluated for its impact on its related cells (see above). The evaluation is now reversed. Each cell that is not a result cell is evaluated for whether a result cell eliminates the availability of that specific Sudoku Id. Inevitably, each Sudoku Id must be evaluated separately. See the table below. Where the summary table (the right hand column) is greater than zero, Sudoku identity 3 is not available to that cell.


In practice we start with a table of results that looks like this one immediately below. The row at the top refers to the individual Sudoku identities.


And end with a table of deletions that looks like this.



This is then combined with the original Sudoku puzzle to look like this,

This columnar presentation then has to be converted back to the standard looking Sudoku puzzle and will look as shown below.

My personal convention is to show result cells with a green background, the values of the original puzzle in red script, those that are user calculated in black script and the unsolved cells with a white background.

Monday, 6 February 2017

Hard Sudoku

Saturday, 4 February 2017 and the Guardian reports how the EU leadership criticise the US president for his ‘lack of respect’. Earlier this week Jeremy Corbyn had placed a ‘3-line whip’ on Labour MPs to support the 2nd reading of the European Union (Notification of Withdrawal) Bill. Quite a number defied the whip but not enough. Typically only Ken Clarke from the Tory side voted against.

It is sad that so many of the UK’s MPs have forgotten that they were voted into Parliament to act in accordance with the views they presented at the General Election 2015. Of course they are entitled to change their mind, but they must be convinced of the rationale for changing it. I wonder whether they are following what they believe to be the voter arithmetic rather than being persuaded by the argument. If that is the case, they have failed fundamentally in their duty as MPs.

But enough of all that. Hard Sudoku is what this blog is about. I tried the Guardian Sudoku puzzle (No. 3663) and recorded my progress. This record uses the methodology developed in MS Excel about 10 years ago. The methodology is quite weird and long winded but, in this context, it is useful for explaining how and why I got stuck. I describe below the argument I employed for getting unstuck.


I had got as far as here using conventional arguments and methods. Assuming no mistakes in recording, this would represent a valid solution thus far. 

The record shows that I had reviewed all the unsolved positions in the puzzle without making any further progress. This also covered the examination of individual Sudoku Nos. and their relative positions in each of the 3 dimensions of the puzzle. There were only four numbers left (1, 2, 6 and 8) and the reasons for not making progress bears some examination.


Top 3 Rows
Middle 3 Rows
Bottom 3 Rows
Left 3 Columns
Middle 3 Columns
Right 3 Columns
Id 1
No positions fixed
All positions fixed
Only 1 position fixed
2 positions fixed?
All positions fixed
Only 1 position fixed
Id 2
No positions fixed
Only 1 position fixed
All positions fixed
2 positions fixed?
Only 1 position fixed
All positions fixed
Id 6
No positions fixed
Only 1 position fixed
Only 1 position fixed
Only 1 position fixed
Only 1 position fixed
No positions fixed
Id 8
Only 1 position fixed
No positions fixed
2 positions fixed?
2 positions fixed?
No positions fixed
Only 1 position fixed

Both Id’s 1 & 2 have a combination groups of three such that one group is solved completely and at least one other group where two positions are known, but not the third. Unfortunately, the information in the opposing dimension does not help us limit the position of the 3rd ID. For ID 6, there is altogether too little information to make further progress.

ID 8 has 2 groups of three where the location of two of the elements is known. At first glance, the unsolved positions in the first column at rows 4 and 5 look the most promising. After all the sub-grid at the bottom left hand corner is solved completely and the sub-grid immediately above it has only these two elements unsolved. Nevertheless, no amount of examination has revealed a logically provable determination as to which cell should contain which ID.

I’m left with considering the position of ID 8 in the bottom 3 rows of the puzzle. At first glance this looks considerably less promising than the column 1 vacancies discussed above, especially since there are 3 unsolved ID positions for row 9 (see the values highlighted in green below) rather than the just two for column 1. However, in my solution methodology, I highlight cells where the possible solutions is limited to 1 of 2 IDs (see the cells circled in red).



The information available to us is therefore is
  • ·         The location of ID 8 is known to be row 8 in for the bottom left sub-group and in row 7 for the bottom right sub-group, thus ID 8 must appear in one of the 3 cells in row 9 of the bottom middle sub-group. The right most cell of this sub-group row 9 has already been solved.
  • ·         There are 3 unallocated cells in row 9 and 3 unallocated IDs.
  • ·         But column 4 of the puzzle has two cells (named 2_43 and 8_48 in my nomenclature) whose ID values are limited 1 or 6. Logically, if these two values must appear in the two named cells, then they cannot appear anywhere else.

I conclude therefore the cell named 8_49 in row 9 must contain the ID value 8, since that is the ID value left for this column.

This is something of a tortuous argument but it seems to work. Any thoughts?


Sunday, 13 November 2016

Early Musical Influences

Following on from bringing my grandchildren to their firstlive concert, I began to wonder what the impact of my own early musical experiences had been.

Clearly my father’s piano playing and the church played an important part (see Getting into Music). The question here is “to what extent have I guided my own musical taste?” as opposed to having it preordained by early experiences.

My teenage years coincided with the ‘Swinging 60's’. The Beatles, The Rolling Stones and the Kinks permeated my young mind. Some of those influences were surprisingly sophisticated, but by the middle of the 70's I had lost all interest in popular music.

An opposing influence were the long playing records that Cla brought home from the Hendon Record Library. The emphasis was on song, mainly Schubert, but there were other important highlights (e.g. Haydn’s arrangement of “My Mother Bids me Bind my Hair” and Benjamin Britten’s folksong arrangements). Kathleen Ferrier and Irmgard Seefried figured large in this melee.

The records also had an impact on the piano music that Cla bought. These included both Schubert’s song cycles “Die schöne Müllerin” and “Winterreise” as well as the group known as Schwanengesang. There was also a large volume of Schubert’s more popular songs. These represented a wide source of exploration for me looking at the piano accompaniments. (There was a universal hatred of me attempting to sing along with myself.)

My tastes in instrumental music were also influenced heavily by Cla’s acquisitions from the Library. Once again, these were dominated by Schubert the piano sonatas, pieces for 4 hands, the myriad of impromtus and moments musicaux,  the ‘Trout’ quintet (including a wonderful cover cartoon of the string players fishing from a grand piano), the two piano trios Op 99 & 100 and the extra ordinary string quintet (Stern, Schneider, Katims, Casals, Tortelier). The emphasis on chamber music was certainly led by Cla, but I was also introduced to the “Great” C major symphony and the incidental music to Rosamunde.
Hoffnung's Trout Quintet Cartoon

This was quite a narrow fare of influence. It needed some external factors to encourage the expansion of my musical taste.

Two things happened at almost the same time. I began to learn the violin and I joined the Hendon County school choir under the direction of Charles Western. To the casual observer, Charles was a mild mannered man who presented a weak persona together with his sloping shoulders. Much fun was made of him, but he had true intellectual integrity in the way he presented choral music.

Naturally, the choir worked on the major choral works in English – Messiah, the Creation (Haydn). On the lighter side, he schooled us in Gilbert and Sullivan (I certainly remember Iolanthe, but I suspect there were others lost in the mists of my memory). His passion, however, was for the English madrigals. These were both great fun and contain sufficient technical difficulty to keep the choir’s collective nose to the grind stone.

Charles was well respected and influential as a choirmaster. He brought the school choir onto the BBC at least twice during my time. The highlight, for me, was the choir trip to Berlin, where Gertrude Stranz (Head of Chemistry) had contacts in Neukölln. The trip, long before the opening of the Channel Tunnel, was an adventure in itself. Charles never lost an opportunity to promote his choir. We sang from memory on board the cross channel ferry. The choir gave two concerts at the school in Neukölln. We also had the opportunity to explore Berlin. This included a brief strip across the wall into East Berlin (as it was then). To us westerners, this was a strange rather unsettling experience.

Learning to play the violin, was every bit as difficult as one might expect. Those hearing me practise must have had sore ears for quite a while. Curiously, I joined several orchestras but never really joined in. The process of working with other musicians meant that I had to submit to a communal discipline, especially with regard to bowing. This was something I have never quite achieved. I’m not deliberately or intentionally anarchic, it comes quite naturally.

Even so from the fringes of the various groups, got to know a much wider range of orchestral music. The highlights of these were the early Schubert symphonies (in particular 3 and 5), the Mozart A major Piano Concerto (No. 23), Mendelssohn’s Oratorio “Elijah”, Elgar’s “Pomp and Circumstance” marches and, almost inevitably, the orchestral accompaniment to Handel’s “Messiah”. I also took this enforced expansion of my musical horizon into other areas of chamber music.

Two groups of works made a profound impression. These were the Beethoven “Rasumovksy” quartets (Op 59) and perhaps most importantly the Haydn string quartets Op 20 (also known as the Sun Quartets). The name “Sun Quartets” may well be made up, even so it is entirely appropriate. 

These quartets brought the genre out from background salon music into an era where the listener was expected to listen attentively. The form of the music was still in its early stages. The 1st violin was very prominent especially in the slow movements. The more egalitarian employment of all four instruments would develop in Haydn’s later quartet writing. Even so these 6 quartets are all emotionally powerful presentations and clearly a precursor to his “Sturm and Drang” period.



This started on me on a musical exploration that continues to this day.

Friday, 11 November 2016

Getting into Music

For me it began at home. Cla played popular songs from WW2 and Stephen Foster songs from the US on the piano. These were my earliest musical memories, caught on the verge of sleep. Setting dates to early memories is really difficult, but I guess these were when I was between 5 and 8 years old.

Curiously, I never remember my two eldest brothers at piano practice, I guess they must have learnt although neither of them play now. My closest brother, David, is a different matter. David is 5 years my senior. An age gap where the younger sibling can be real nuisance. I fitted that category to a ‘T’.

Initially, David took piano lessons from a teacher near his school in Hendon. While I certainly heard him practice, it made no impression on me. As I reached 10, my father wondered whether I would take to having lessons myself. David at that time was having lessons from Dennis Page at home. Dennis lived in Harrow and arrived at the house on a moped. It’s curious how these memories stick.

There was some doubt as to whether I had either the talent or was suitable for teaching. It turned out that I had very little talent, but plenty of energy and interest in music. When teaching young children that counts for a lot.

The initial stages must have been difficult for Dennis, but over the course of time I learnt my way around a keyboard. Reading music, and in particular sight reading, never came easily. Here my elder brother David was an absolute boon. He, of course, was practising a way at his pieces while I listened. This was not an intentional or surreptitious listening. It was simply part of life at home. I confess, however, when I got bored with practising my own pieces, I grabbed his and learnt them.

This was a cause of deep irritation for David. I gather he complained quite forcefully to Cla and Rose. But I was not very controllable and, in any case, whole episode was one of the primary reasons that I learnt to read music effectively. I have benefited hugely from this learning, in particular from the intimate knowledge of both the base and treble clefs. I still play the piano, albeit, very badly.

Dennis and his sister were both music teachers. Together they provided a platform for us young students to present our progress in performance. I remember being driven to Harrow and waiting with lots of other would be pianists (and the occasional violinist).

One of the most nerve racking experiences was the wait until I was called on stage. We young performers were in the wings of a theatre stage, trying (and in my case failing) to remain calm. I remember going through this process at least twice. At the last of these, I performed the “Gypsy” rondo from one of Haydn’s piano trios arranged for solo piano. (I still have the sheet music in my library of piano music.) Although it looks simple, it really isn’t.

Here I show images of the initial bars of the rondo up to the first recapitulation from two different sources. The first is 18 bars long and the second 26 bars. Broadly speaking rondos have standard structure of A – B – A – C – A. Second source shows the full rondo structure in its full glory, while the first is adapted for students at around grade 5 and entirely misses out two of the sections as well as some of the elements of the musical development.

Simplified version

Full Version

I highlight below some of the more interesting sections omitted from the student version.



Note the rather tricky rhythmic passages in the left hand of the second passage. I have certainly never managed this effectively on the piano. But this is taken up by the cello in the trio version and is much easier.

Notwithstanding that I eventually became a committed atheist, I attended church at Mill Hill until I was 16. Curiously, my weekly dose of hymn singing made very little impact on me. However, I was asked to accompany small children on piano at Watling Free Church during my later teens. This meant that I had to come to grips with a new hymn more or less every other week. This requirement to lead the children in, and to persuade them to sing with me was a very useful skill.


Although perhaps the most useful skill was being able to fudge the difficult bits. I’ve applied it ever since.

Sunday, 6 November 2016

Live Music and Children

About a week ago I attended a workshop for orchestral instrumentalists organised by the Pulham Village Orchestra (PVO). These are biennial events and I attended my first in 1988, when my own children were all 10 or less.

This year the study pieces included Vaughan Williams, Symphony No.8; extracts from Hansel and Gretel, Humperdinck (including a couple of songs) and overture to Semiramide by Rossini. I expected the Vaughan Williams to be very challenging and indeed it was. After three fairly full days of rehearsal work, both as a whole orchestra and with tutors for individual sections we gave a performance. This time three generations of Allens and Walshes attended the concert.


Bryony, Meredith and Simon had their work cut out keeping Molly (8) and Nuala (4) from causing disruption, but they managed it. At least I didn’t notice anything untoward from the depths of the viola section. However, the order of the concert was geared for the benefit of the amateur players and our powers of concentration, rather than the audience.

The Vaughan Williams Symphony came first. It was something of a struggle for both audience and players. However the 3rd slow movement for strings only came over well. It certainly attracted Nuala’s attention. That’s quite a feat for a four year old.

Everybody enjoyed the singing in the Humperdinck but the purely orchestral parts did not come over as well.

Molly’s attention was really engaged by a recently composed duet for percussion. Unfortunately, neither the details of the performers nor the composer were included in the programme. I’ll try and locate these details over the next couple of weeks.

The Rossini overture was also attractive for both the children. Even so, I gather that their attention was beginning to waiver. From their viewpoint it would have been better received at the start of the concert (as implied by its title).


Molly and Nuala were by no means the only youngsters at the concert. It is important that they hear presentations of all sorts of standards. They will be much better able to appreciate what’s really good later on.