Checking values within range/array

Beachcomber

New Member
Joined
Oct 12, 2005
Messages
12
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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Beachcomber

New Member
Joined
Oct 12, 2005
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,291
Messages
5,571,331
Members
412,382
Latest member
Langtn02
Top