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:
sudokuexample.jpg


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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,163
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top