I started fiddling around with a Sudoku spreadsheet this week. I got a Sudoku book in my Christmas stocking. I didn’t have terrible difficulty solving most of the puzzles, but it’s obvious after the first one that Excel could be a nice tool for keeping track of which numbers can and cannot go in certain spaces.
First I downloaded Andy Pope’s Sudoku workbook and the one on Office Online. The former is a masterful piece of coding, but if you’ve ever seen Andy’s work before, you probably won’t be surprised. I plugged in the toughest puzzle from the book, even unintentionally omitting one number, and it solved it in about three milliseconds. The latter workbook was equally intriguing because it used circular formulas and no VBA. It could not, however, solve the super tough puzzle from the book.
I’ll discuss this workbook more when I post it, but on to my immediate problem. I have a range name that contains noncontiguous cells called Grid1. It refers to
and I want to use it in a conditional formatting formula. Apparently COUNTIF doesn’t cotton to noncontiguous ranges. Juan Pablo Gonzalez says it can be done but his example uses multiple contiguous ranges, whereas I have just one range. INDIRECT apparently doesn’t like noncontiguouosity either because I couldn’t even get =SUM(INDIRECT("grid1")) to work, although =SUM(Grid1) works fine.
The formula I’m trying to enter in conditional formatting is =OR(ISNUMBER($B$2),COUNTIF(Grid1,B1)>0,COUNTIF(Row1,B1)>0,COUNTIF(Col1,B1)>0). Basically, it means that if there’s a number in B2, a 1 in Grid1, Row1, or Col1, then format B1 (which contains a 1). I’m not interested in creating 81 (9×9) unique conditional formatting formulas, but if I can’t solve this I’ll probably write a macro to create the formulas. That should only take about twice as long as doing it manually, assuming I could do it without errors. Any ideas?
You could write a user-defined function for just the counting of the non-contiguous range of cells that match the criteria:
Function CountifNC(rInputRange As Range, sCriteria As Single) As Single
Dim i As Integer
Dim c As Range
i = 0
For Each c In rInputRange.Cells
If c.Value = sCriteria Then i = i + 1
Next
CountifNC = i
End Function
The formula for the conditional formatting of course becomes:
=OR(ISNUMBER(B2),COUNTIF(Row1,B1)>0,COUNTIF(Col1,B1)>0,CountifNC(Grid1,B1))
I could only get it to work if the user-defined function was the last argument passed in the OR function.
When you get that solved, try the 15×15 SuDoku puzzles?
I too have been recently bit by the SuDoku bug and solved most of the puzzle reasonably rapidly (about 8 to 10 minutes each) in a little stocking stuffer. So, I went to the internet to look for a ‘free’ generator for puzzles and fount those 15×15. I literally went crosseyed – Yikes!
Wow. That is an interesting one.
I haven’t had much luck so far, but have found that you can use =INDEX(grid1,,,1) to iterate through the cells in the range (e.g. =INDEX(grid1,,,2), etc.). However, I haven’t been able to combine it with anything useful so far.
Dick,
I don’t have a solution to your contiguosity problem, but I have two ideas:
1. Would some of the fancy SUMPRODUCT with double minus signs work on non-contiguous ranges?
2. How about building a named formula with relative references as the input formula in the conditional format equation? (Haven’t tried this myself).
HNY to all the DDE blog folks!
Dick,
I don’t know if this will help (I have not messed with Soduku yet), but to mimic countif(Grid1,B1), I used
=SUMPRODUCT((MOD(ROW(EntireGrid)-2,5)*MOD(COLUMN(EntireGrid)-2,4)=0)*(LEN(EntireGrid)>0)*(EntireGrid=B1))
since your Grid1 cells are evenly spaced along the rows and columns. EntireGrid is a defined name range =Sheet1!$B$2:$L$10 encompassing all the Grid1 cells.
[…] I also found a Sudoku puzzle solver at Office Online via Daily Dose of Excel. […]
Dick,
I noticed an error in my function, the formula should be:
=SUMPRODUCT((MOD(ROW(EntireGrid)-2,4)=0)*(MOD(COLUMN(EntireGrid)-2,5)=0)*(LEN(EntireGrid)>0)*(EntireGrid=B1))
Hi Dick,
Try laurent laungre’s “Join Array” function. That might be of help.
Find the same at
Laurent Longre http://xcell05.free.fr
This is great stuff…