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


--
webby62
------------------------------------------------------------------------
webby62's Profile: http://www.excelforum.com/member.php...o&userid=24441
View this thread: http://www.excelforum.com/showthread...hreadid=380395

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: FormatConditional Formatting
=COUNTIF($A$5:$A$13,A5)1
Insert in A5 (here) and then Copy and Paste format

Alt 3: Menu: DataValidation
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...tid=3515&stc=1


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=380395

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

--
HTH

Bob Phillips

"webby62" wrote in
message ...

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


--
webby62
------------------------------------------------------------------------
webby62's Profile:
http://www.excelforum.com/member.php...o&userid=24441
View this thread: http://www.excelforum.com/showthread...hreadid=380395

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


--
webby62
------------------------------------------------------------------------
webby62's Profile: http://www.excelforum.com/member.php...o&userid=24441
View this thread: http://www.excelforum.com/showthread...hreadid=380395

Ok, try this formula in Conditional Formatting:
=COUNTIF($A$1:$C$3,A1)<1

HTH
Ola Sandström


Note:
Zip-file:
http://www.excelforum.com/attachment...tid=3517&stc=1
Video:
http://www.datapigtechnologies.com/f...rmatexcel.html


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=380395

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...tid=3518&stc=1
Video:
http://www.datapigtechnologies.com/f...rmatexcel.html


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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=380395

webby62 wrote:
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



=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.

"webby62" skrev i en
meddelelse ...

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


--
webby62


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 :-)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Thankyou for your time and help regards kevin


--
webby62
------------------------------------------------------------------------
webby62's Profile: http://www.excelforum.com/member.php...o&userid=24441
View this thread: http://www.excelforum.com/showthread...hreadid=380395

You're welcome, Kevin :-)

LeoH


"webby62" skrev i en
meddelelse ...

Thankyou for your time and help regards kevin

Here is zip-file with the Sudoku game.
The 9X9 square can be expanded to a 18X18 square or bigger.

Good thinking
Ola Sandström


Attachment:
http://www.excelforum.com/attachment...tid=3580&stc=1
Link: http://www.andypope.info/fun/sudoku.htm


+-------------------------------------------------------------------+
|Filename: Sudoku.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3580 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=380395