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
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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks