An "Excel' puzzle-solver for SuDoku

Programs which generate, solve, and analyze Sudoku puzzles

An "Excel' puzzle-solver for SuDoku

Postby mannyoh » Tue Oct 15, 2013 11:41 pm

From: MANUEL SLAWKIN [mailto:masmpr@---.net]
Sent: 05 October 2013 21:38
To: andrew@---.com
Subject: A "Solver"

Briefly, I am an 80 year old guy who 3 years ago was volunteering at a desk in a local charity ... having little to do each day, waiting for phone calls, I came across a SuDoKu puzzle in a magazine and was "hooked" immediately ...

Being an ardent Excel user in my pre-retirement Manufacturing/Management Consulting work, I started to attempt to develop an "Excel' puzzle-solver for SuDoku ... I probably should have googled to see if there WERE solvers out there [whick I'm now certain there are many of them], but I didn't ... After a while, I was getting close to something potentially useful for me when the volunteer work got a bit busier so I stopped the Excelling, so to speak ...

Anyhow, I started the solving-exercise again last month -- and, I think I may really have a nice "tool" to share, BUT none of my family/friends is interested in SuDuko or ANYTHING that I do with ANY Excel stuff ... So, is there some forum or something where I might share my ideas? ...And if you DO read [and possibly respond to] this unsolicited Email ... Thank you .. Manny
Last edited by mannyoh on Fri Oct 25, 2013 1:56 pm, edited 1 time in total.
mannyoh
 
Posts: 13
Joined: 15 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby spnotation » Wed Oct 16, 2013 3:37 am

Hi Manny,

I can review your Excel solver. Where can I find it?
spnotation
 
Posts: 2
Joined: 16 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby mannyoh » Wed Oct 16, 2013 6:43 pm

Thanks .. i'll try to figure out how to send it
Last edited by mannyoh on Fri Oct 25, 2013 2:01 pm, edited 2 times in total.
mannyoh
 
Posts: 13
Joined: 15 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby enxio27 » Thu Oct 17, 2013 1:07 pm

mannyoh wrote:...guess my ignorance keeps coming to light ... _ I'm fairly computer-literate of one my age, but I'm what you guys probably call a NOOB[?] at this forum stuff...still learning the protocol...do I just email my spreadsheet as an attaschement HERE or in some other way .., and I suppose I should write "instructions/processes" to go thru ...???

I don't believe this forum allows attachments to posts, but you might send a private message to the forum administrator, Jason Lion (click on the "0 new messages" underneath the forum header) and ask whether he could host your file for people to download. It would probably be good to include a text file with your instructions and processes.
User avatar
enxio27
 
Posts: 352
Joined: 13 November 2007

Postby Pat » Thu Oct 17, 2013 1:37 pm

enxio27 wrote:
mannyoh wrote:---my spreadsheet as an attaschement HERE or in some other way

I don't believe this forum allows attachments to posts---

use "Upload attachment"

a ZIP-file probably can be attached to any post
User avatar
Pat
 
Posts: 3449
Joined: 18 July 2005

Re: An "Excel' puzzle-solver for SuDoku

Postby JasonLion » Thu Oct 17, 2013 3:16 pm

Attachments to posts are allowed. The file size is limited to 256K, which shouldn't be an issue here.
User avatar
JasonLion
2017 Supporter
 
Posts: 625
Joined: 25 October 2007
Location: Silver Spring, MD, USA

Re: An "Excel' puzzle-solver for SuDoku

Postby mannyoh » Thu Oct 17, 2013 6:52 pm

I've been trying to make it "fail" then modifying formulae ... and I've used ALOT of Conditional Formatting to help identify possible moves ... unfortunately, the SS size will be about 1 MB or maybe larger :| I am writing up procedures for ss use, and some are optional, as well as explanations of the various formula/hint cells .....when I'm finished, I'll attempt to get the ss and procedures to the forum somehow...Thabks in advance for any help ... Manny
Last edited by mannyoh on Fri Oct 25, 2013 1:59 pm, edited 1 time in total.
mannyoh
 
Posts: 13
Joined: 15 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby mannyoh » Wed Oct 23, 2013 9:58 pm

I have been working on many "very hard" puzzles in
http://www.dailysudoku.com/sudoku/index.shtml ... been able to SOLVE them when I FOLLOW my own rules ... my Excel indicates when I mahe a wrong move ... able to retrace steps to see where/why wrong move was made ... sometimes an error in forumulas was indicated, allowing me to correct ...

I think model is pretty good, hoping to find SuDoku playes to critique ... I'd appreciate ANY feedback ... still not sure how to get the Model to someone Thanks, in advance...Manny
Last edited by mannyoh on Fri Oct 25, 2013 2:06 pm, edited 1 time in total.
mannyoh
 
Posts: 13
Joined: 15 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby daj95376 » Thu Oct 24, 2013 12:42 am

The "very hard" puzzles on the Daily Sudoku website are considered "very easy" at this website.

Try this puzzle. It's from an old post of mine in the Discussions section/forum of that website.

Code: Select all
 +-----------------------+
 | . . 4 | 5 . 6 | . 9 8 |
 | . . 2 | 4 . . | . . 7 |
 | 6 7 5 | . . . | . . 4 |
 |-------+-------+-------|
 | 9 5 . | 6 8 . | . . 3 |
 | . . . | 7 3 . | . . . |
 | 7 . . | . . 9 | . . . |
 |-------+-------+-------|
 | . . . | . . . | 9 . 2 |
 | 3 . . | . . . | . 4 . |
 | 5 2 9 | 3 . . | 8 . . |
 +-----------------------+
daj95376
2014 Supporter
 
Posts: 2624
Joined: 15 May 2006

Re: An "Excel' puzzle-solver for SuDoku

Postby mannyoh » Thu Oct 24, 2013 11:54 pm

I have just solved the puzzle by carefully following my own "rules" I just don't know how to send my (Now) Zipped Excel file so you can see my model .. I'm still learning how to attach a [zipped] file in these replies...

+-----------------------+
|1 3 4 |5 7 6 |2 9 8 |
|8 9 2 |4 1 3 |6 5 7 |
|6 7 5 |2 9 8 |3 1 4 |
|-------+-------+------|
|9 5 1 |6 8 4 |7 2 3 |
|2 4 8 |7 3 5| 1 6 9 |
|7 6 3 |1 2 9 |4 8 5 |
|-------+-------+------|
|4 1 6 |8 5 7 |9 3 2|
|3 8 7 |9 6 2|5 4 1|
|5 2 9 |3 4 1 |8 7 6|
+-----------------------+
mannyoh
 
Posts: 13
Joined: 15 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby mannyoh » Thu Oct 24, 2013 11:57 pm

where do I find "upload attachments"????
ok...i just see the link below but still need help there ...
mannyoh
 
Posts: 13
Joined: 15 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby JasonLion » Tue Oct 29, 2013 4:46 pm

mannyoh e-mailed me his Excel based Sudoku helper. At his request, I have attached it to this post so everyone can look at it.
Attachments
SuDokuModel.xls.zip
(86.76 KiB) Downloaded 209 times
User avatar
JasonLion
2017 Supporter
 
Posts: 625
Joined: 25 October 2007
Location: Silver Spring, MD, USA

Re: An "Excel' puzzle-solver for SuDoku

Postby spnotation » Sat Nov 02, 2013 4:09 am

The puzzle solver is quite colorful and I'm just glad that the colors aren't too bright. I've seen spreadsheets that cause quite an eyestrain from bright and clashing colors.

You should blank out the candidates (possible values) if the cell is filled. You can do this by adding a not(isblank(xx)) condition. An example would be the formula for H14 (candidate "4") for cell K14

=IF(OR(NOT(ISBLANK(K14)),K2=4,K6=4,K10=4,K14=4,K18=4,K22=4,K26=4,K30=4,K34=4,P14=4,U14=4,Z14=4,AE14=4,AJ14=4,AO14=4,AT14=4,F14=4,P18=4,P22=4,F18=4,F22=4),"",4)

Actually, the condition K14=4 above is redundant, but it is easier to retain it to make the formula easier to copy.

Here is another way to do this:
= if(and(iserror(find("4", concatenate(K2,K6,K10,K14,K18, .... ,F22)), not(isblank(K14))), 4,"")

The concatenate() function converts numbers to text and blanks to "", which makes the find() function work like the OR function. It just makes it more elegant to work with a list of cells instead of a series of = conditions.

-------------------------------

The vertical merged cells should use the count() function instead of the sum() of 0s and 1s in the blocks below. E2 should be =count(b2:d4) instead of =sum(b47:c49). You don't need those cells of 0s and 1s and you can use the space for other things ....

--------------------------------

The vertical and horizontal merged cells provides helps with rows and columns, but not on the 3x3 blocks. To do the block helper, you can add another band, just like b39:at44, for the 3x3 blocks.

The B39 formula =COUNTIF(B2:B36,"=1") will be the pattern, such that
B47 formula = COUNTIF(B2:D12,"=1") + COUNTIF(G2:I12,"=1") + COUNTIF(L2:N12,"=1")

-------------------------------

You can add a feature to highlight the remaining possible locations of a number. If I want to analyze 8, I can type in 8 in A1 and this will cause all 8 possibilities to change color.

-------------------------------

I use certain techniques to eliminate candidates. It would be useful if possibilities can be "eliminated" by deletion or by "x" and restored by copying an adjacent cell. It would take a lot of work to make the formulas more "copyable". Absolute cell references like $B$2:$D$12 will be needed, as well as formulas for the candidate numbers 1 to 9, by using the mod(), row() and column() functions.

As it is, the only way to restore the candidate cells back to the original formula would be to copy them from a master worksheet.

You can find the elimination techniques in my 2011 blog: spnotation.blogspot.ca. I used A1 notation for cell references, just like Excel, since I started out with an Excel solver like you.

--------------------------------
spnotation
 
Posts: 2
Joined: 16 October 2013

Re: An "Excel' puzzle-solver for SuDoku

Postby civiliza » Mon Nov 04, 2013 8:11 am

Haven't really had a chance to look at many of the formulae, however there is a noticable discrepancy in the AU to AW cells.

They use counts of specific digits across their respective rows, but the top row of each 3x3 set (ie rows 2, 6, 10 etc) includes both the input / given cell, and the "remaining digits in this cell" (columns E, J, O etc) counts.

A given/input value of 1, 4, or 7 will produce a count of 1 against the respective digit, while any other digit produces a count of 0.

Likewise "remaining digits in the cell" values of 1, 4, or 7 will also produce extra false digit counts in the AU to AW cells.

Note - All cell's referred to are from the Current Mod worksheet.
civiliza
 
Posts: 64
Joined: 25 October 2010

A Very Graphic Excel That HELPS You Solve

Postby mannyoh » Thu Apr 03, 2014 5:02 pm

The Excel program I've been working on [I'm an 80 year old "Noobe"] really makes the analysis very helpful ..

Though it is NOT an INSTANTANEOUS solver, it does let the user WORK to a solution of any puzzle up to the more technical diabolical ones ...

If you are interested in trying it -- and hopefully improving it -- please contact me with a posting here, a private message or a direct Email to masmpr@ameritech.net ...

Thank you for you possible interest ...

mannyoh
mannyoh
 
Posts: 13
Joined: 15 October 2013


Return to Software