soduko

W

webby62

Is there a way of checking the whole nine boxes individually so tha
there are no two numbers the same in each box? i have done the row
and columns with countif but need each separate box to show if the
are doubled thank
 
O

olasa

I'm not sure what your purpose is, so here are 3 alternatives:

Alt 1: Count the number of 'non single items'
=SUMPRODUCT((COUNTIF(A5:A13,A5:A13)>1)/2)

Alt 2: Menu: Format>Conditional Formatting
=COUNTIF($A$5:$A$13,A5)>1
Insert in A5 (here) and then Copy and Paste format

Alt 3: Menu: Data>Validation
Mark the whole area from A5 to A13 (here), then insert this formula
=COUNTIF($A$5:$A$13,A5)=1

Hope this helps
Ola Sandström

Enclosed zip-file:
http://www.excelforum.com/attachment.php?attachmentid=3515&stc=1


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3515 |
+-------------------------------------------------------------------+
 
B

Bob Phillips

Assuming the number are in A1:I9, put this formula in M1, and copy down and
across to U9

=COUNTIF(OFFSET(INDEX($A$1:$I$9,INT((ROW(A1)+2)/3)*3-2,INT((COLUMN(A1)+2)/3)
*3-2),,,3,3),A1)>1
 
W

webby62

thanks for replying but im looking to check numcbers that are matching
in say A:3+B:3+C+3:then another section of boxes D:3+E:3+F:3 in
numerous boxes but obviously each box individually

1 8 5
*2 2* 6
3 4 7
 
O

olasa

O

olasa

Ok, try this formula in Conditional Formatting:
=COUNTIF(OFFSET($A$3:$C$3,0,INT((COLUMN()-1)/3)*3),A3)<>1

HTH
Ola Sandström


Note:
Zip-file:
http://www.excelforum.com/attachment.php?attachmentid=3518&stc=1
Video:
http://www.datapigtechnologies.com/flashfiles/conditionalformatexcel.html


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3518 |
+-------------------------------------------------------------------+
 
A

Aladin Akyurek

webby62 said:
thanks for replying but im looking to check numcbers that are matching
in say A:3+B:3+C+3:then another section of boxes D:3+E:3+F:3 in
numerous boxes but obviously each box individually

1 8 5
*2 2* 6
3 4 7
Click to expand...

=1-(COUNT(1/FREQUENCY(A1:C1,A1:C1))=COUNT(A1:C1))

1 as result means duplicates in the record, 0 no duplicates. A totally
empty record will be treated as one with no duplicates.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
L

Leo Heuser

webby62 said:
Is there a way of checking the whole nine boxes individually so that
there are no two numbers the same in each box? i have done the rows
and columns with countif but need each separate box to show if they
are doubled thanks
Click to expand...


Webby

Assuming Sudoku square in A1:i9, enter this formula in e.g. K1:

=IF(ISERROR(MODE(OFFSET($A$1:$C$3,(ROWS($K$1:K1)-1)*3,
(COLUMNS($K$1:K1)-1)*3))),"No dups","Dups")

Copy K1 to K1:M3

K1 will check for duplicates in the square A1:C3, L1 for duplicates in
D1:F3,
K2 for duplicates in A4:C6 etc.

Nice game Sudoku :)
 
O

olasa

Top