- Incomplete solution for the more difficult sample
Hi. Villerory.
Thank you for your attachment of the mock-up Sudoku solution, Sudoku.ods.
First, I noted that this is only a mock-up, and not a solution.
Next, how do you get/make the array formula {=SUDOKU(A4:I12)}?
Intuitively, I always thought that to solve the Sudoku, we have to find the relationship between the empty cell to the row, or column, or 3x3 block that contains the cell. Is it possible to formulate a custom array formula to do just that?
In my solution, I employed 2 pencil and paper operations:
- 1. Delete all digits in the row, column, 3x3 block from 123456789 and keep the remaining digits in the cell being processed.
2. Search for a hidden single digits in the [remaining digits] in all the cells in a row, column, or 3x3 block. If found, update the content of the cell.
Are we able to employ some STRING FUNCTIONS or built-in cell-formulae inside the ARRAY FORMULA?
To repeat Step 1 and Step 2 above, I can use a LOOP in the macro to achieve that. If we do it manually on a worksheet, I guess we have to execute the array formula repeatedly down the worksheet, and manually inspect the result to know when to stop, where repeating the 2 steps won't yield any more [clue], that is a single digit in a cell.
Or, do you have any known array formula combined with any built-in cell formulae that can do the similar things for Step 1 and Step 2?
The learning curve for the above is simply too steep for the time being.
I am taking a closer look at your earlier attachment Sudoku1.ods.
I now isolate the cell-formulae used by looking them up in Google search:
DEC2BIN, RIGHT, SUBSTITUTE, MID, SEARCH.
Of these, 3 are new to me. Looking them up in the OO Documentation/howtos/functions yield the following.
(Now the codes don't look so intimidating.)
DEC2BIN
Converts a decimal number to binary.
This function is only available if the Analysis AddIn is installed.
Syntax:
DEC2BIN(number; numdigits)
returns a binary number as text, given the decimal number, which must be between -512 and 511 inclusive, and may be text or a number.
The output is a binary number with up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal).
numdigits is an optional number specifying the number of digits to return.
Example:
DEC2BIN(9)
returns 1001 as text.
SUBSTITUTE
Substitutes new text for old text in a text string.
Syntax:
SUBSTITUTE(originaltext; oldtext; newtext; which)
In originaltext, removes oldtext, inserts newtext in its place, and returns the result. oldtext and newtext can have different lengths.
which (optional) is a number which specifies which occurrence of oldtext to replace (counting from the left). If omitted, all occurrences are replaced.
Example:
SUBSTITUTE("castle"; "stl"; "v")
returns cave.
SEARCH
Returns the position of a string of text within another string.
Syntax:
SEARCH(findtext; texttosearch; startposition)
returns the character position of the first occurrence of findtext within texttosearch.
startposition (optional) is the position from which the search starts.
The search is not case-sensitive.
The search will use regular expressions, if they are enabled (Tools - Options - OpenOffice.org Calc - Calculate).
A failed search gives the #VALUE! error.
In Tools - Options - OpenOffice.org Calc - Calculate the setting for Search criteria = and <>must apply to whole cells has no effect.
Example:
SEARCH("yo"; "Yoyo")
returns 1. The search is case-insensitive.
SEARCH("cho"; "choochoo"; 2)
returns 5.
SEARCH("t.n"; "often")
returns 3, if regular expressions are enabled. The "." stands for any single character in a regular expression, so "t.n" matches "ten".
SEARCH("xyz","abcdef",1)
returns #VALUE!.
NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.
I tested the solution with 2 samples of Sudoku puzzles. For the simple sample it worked beautifully. The solution fell short for the more difficult puzzle where a choice between 2 digits, and applying depth-first search and backtracking is required and repeated for a couple of cycles.
So that is the limitation of cell-formula approach.
Please see the attachment for the results of the 2 trials.