soduko

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 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" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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

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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>

"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

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.

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> 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.

You're welcome, Kevin :-)

LeoH


"webby62" <[email protected]> skrev i en
meddelelse news:[email protected]...
>
> Thankyou for your time and help regards kevin
>