Checking values within range/array

[Beachcomber]

Hello! I promise what I am trying to do is very simple, I am just looking for some guidance.

I'm putting together a simple "Sudoku" game in excel. For the uniniated, sudoku is a Japanese game which involves placing the numbers 1 - 9 in a 9 x 9 table. [I have already done extensive research on the web and in this forum, my question is very simple, I promise] Soduku has three conditions:

1. The numbers 1 through 9 can only appear in each column of the table only once
2. The numbers 1 through 9 can only appear in each row of the table only once
3. Each 3 x 3 region (which I've marked with the letters A through I), can only have one occurence of the numbers 1 through 9

Here is my current setup, for example only:

Here is what I'm trying to do:

1. The user can put numbers into this 9 x 9 table wherever they want

2. I want to create a simple formula which checks each column of my table to make sure that the numbers 1 through 9 (1,2,3,4,5,6,7,8,9) occur only once (one formula for each column)
3. A similar formula to check each row of the table make sure that the numbers 1 through 9 occur only once (one formula for each row)
4. For each 3 x 3 subdivision in the table, (denoted by letters in my example) it checks to make sure numbers 1 through 9 occur only once (one formula for each region)

Finally, I have a seperate cell which determines if the above conditions are met, and then tells the user if the table "works" or not.

All I'm looking for is a simple way to make sure that the values 1 through 9 occur only once per column, row, and 3 x 3 area. What function should I use to do that? VLOOKUP() and HLOOKUP() seem a little too overkill. Is there a simpler, easier way to do this?

Your help is greatly appreciated - I'm struggling with trying to make this work, and I know it can be really simple. I'm not trying to solve the Sudoku, I'm just checking numbers.

THANK YOU!

 

[erik.van.geit]

Hello, BeachComber,

try this
=SUM(--(COUNTIF(A1:I1,{1,2,3,4,5,6,7,8,9})=1))=9

basically this counts for each number if there is exactly one appearence
then sums the results (TRUE or FALSE which is summed as 1 or 0)
then see if result uquals to 9

kind regards,
Erik

 

[acw]

Hi

This can be adapted to a row, column or square. They are all array entered (ctrl, shift, enter)

Row: =AND(MIN(A1:I1)>0,MAX(COUNTIF(A1:I1,{1,2,3,4,5,6,7,8,9}))=1)
Square: =AND(MIN(A1:C3)>0,MAX(COUNTIF(A1:C3,{1,2,3,4,5,6,7,8,9})))
Column: =AND(MIN(A1:A9)>0,MAX(COUNTIF(A1:A9,{1,2,3,4,5,6,7,8,9}))=1)

It doesn't perform any checking to make sure the cells are all completed before testing. If you put it to a blank row, it will come back false. However, the moment you enter 1 number, it will come back as true, even though the other 8 cells in the row are blank. It may pay to put a generic test on the entire range (say A1:I9 - something like =count(a1:i9)=81) before you worry about the individual row / column / square checks.

HTH

Tony

 

[PaddyD]

you may also be interested in:

http://www.andypope.info/fun/sudoku.htm

...the code isn't protected

 

[Beachcomber]

WOW! This is exactly what I am looking for! I like Erik's formula a lot, and acw your formulas work out great too! I'm going to play around with them to get this whole Sudoku thing resolved. PaddyD --> already checked that out, but thanks for posting it up. Once again, i cannot express how thankful I am for your assisstance. Happy holiday season to you! Blessings.