Count Diagonals

[motilulla]

Hi everyone,</SPAN></SPAN>

The below example I have all 1 I want to count 2 Diagonal and 3 Diagonal as shown</SPAN></SPAN>

Where I need to count diagonal right side of the upper rows and left side of the lower rows</SPAN></SPAN>

For example1: if we check in row 5 we have 3 diagonal with 2 match</SPAN> (first in cell E5=1 and cell F4=1) second in cell J5=1 and cell K4=1, and last (third in cell L5=1 and M4=1) </SPAN></SPAN>

For example1: if we check in row 6 we have </SPAN>3 diagonal with 2 match and 1 with 3 match</SPAN> (first in cell E7=1 and cell E6=1) second in cell F7=1 And cell G6=1, and 3 diagonal match are In cell F7=1, cell G6=1 and Cell E8=1</SPAN></SPAN>

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14	Total match 2 diagonal	Total match 3 diagonal
2	1		1
3	2			1						1					1
4	3					1		1			1		1
5	4	1			1				1	1		1		1		3
6	5				1		1								1
7	6	1		1		1										2	1
8	7				1							1			1	1
9	8	1				1	1	1		1	1					1
10	9				1				1							2
11	10
12	11	1	1		1	1		1	1		1
13	12											1		1
14	13				1		1				1		1			2
15	14		1

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Mr.Excel​

Results are shown in columns P And Q</SPAN></SPAN>

Please help</SPAN></SPAN>

Thanks And Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Jerry Sullivan]

Moti,

For the Left-Diagonal counts:

In P2, and copy down
=IF(ROWS(P$2:P3)<2,0,SUMPRODUCT(--(C3:O3=1),--(B2:N2=1))-Q3)

In Q2, and copy down
=IF(ROWS(Q$2:Q3)<3,0,SUMPRODUCT(--(D3:O3=1),--(C2:N2=1),--(B1:M1=1)))


Also, a correction for the range to reference for the match 2 Right-Diagonal Counts:
In P3, and copy down
=IF(ROWS(P$2:P3)<2,0,SUMPRODUCT(--(B3:N3=1),--(C2:O2=1))-Q3)


If you have Excel 2007 or later, you can use similar formulas with the COUNTIFS() function.

 

[motilulla]

Thank you JS411 for your formula help, it is working perfectly. Actually I am using excel version 2000<o></o>
<o></o>
Regards,<o></o>
Moti<o></o>

 

[motilulla]

Is it possible to have any Function that can count minimum 2 and maximum 4 diagonals being in the left or right side, from a particular row upwards? For example from row 4 going towards row 3, 2, and 1.</SPAN></SPAN>

My data’s are not fixed and I move them in different columns each time. Only with the formula every time I have to change the row and column to get a correct result. But may be with a Function it will be more convenient to get result anywhere in the sheet.</SPAN></SPAN>

Thanks and Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Jerry Sullivan]

Is it possible to have any Function that can count minimum 2 and maximum 4 diagonals being in the left or right side, from a particular row upwards? For example from row 4 going towards row 3, 2, and 1

Could you clarify that description and perhaps provide an example like your previous questions?

Regarding using a UDF (User Defined Function) instead of a formula to allow you to move the cells holding the results, that's a possibility however I think you will be better off managing that with Absolute and Relative references or defined names.

Even a UDF will need to somehow reference the range. The UDF provides you more options for how to do that; however there's techniques that should allow you to use whichever you prefer.

If you'll provide the clarification, I'll try to explain how to manage the range referencing as part of a suggestion solution.

 

[motilulla]

JS411, Thank you once again for you kind help.<o></o>
<o></o>
Example below shows data’s in cells B3:O9 and result match diagonals 2, 3, & 4 is shown in Columns P, Q, & R for match Right Diagonals:<o></o>
<o></o>
Matched Right 2 Diagonals:<o></o>
Match 2 diagonals check row 4 cells B4:O4 with upper row 3 cells B3:O3 <o></o>
And place result in cell P4 <o></o>
<o></o>
Step1:<o></o>
First: match show in cell C4 match with upper row cell D3<o></o>
Second: match show in cell F4 match with upper row cell G3<o></o>
Third: match show in cell J4 match with upper row cell K3<o></o>
And show the result in cell P4 = 3<o></o>
<o></o>
Step2: will check row 4 cells B5:O5 with upper row 3 cells B4:O4 <o></o>
And place result in cell P5 <o></o>
<o></o>
Step3: will check row 4 cells B6:O6 with upper row 3 cells B5:O5 <o></o>
And place result in cell P5<o></o>
<o></o>
Matched Right 3 Diagonals:<o></o>
Match 3 diagonals check row 5 cells B5:O5 with row 4 cells B4:O4 <o></o>
And with upper row 3 cells B3:O3place result in cell Q5 <o></o>
<o></o>
Step1:<o></o>
First: match show in cell E5 match with cell F4 & G3<o></o>
Second: match show in cell I5 match with cell J4 & K3<o></o>
And show the result in cell Q5 = 2<o></o>
<o></o>
And next steps will follow as above will be checked result rows with upper 2 rows<o></o>
<o></o>
Matched Right 4 Diagonals:<o></o>
Match 4 diagonals check row 6 cells B6:O6 with row 5 cells B5:O5 and row 4 cells B4:O4 and last upper row 3 cells B3:O3 place result in cell R6 <o></o>
<o></o>
Step1:<o></o>
First: match show in cell D6 with E5 and with cell F4 & G3<o></o>
And show the result in cell R6 = 1<o></o>
<o></o>
And next steps will follow as above will be checked result rows with upper 3 rows

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14	Matched Right Diagonals
2	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14	2	3	4
3	1			1			1				1		1		1
4	2		1			1			1	1						3
5	3				1				1							2	2
6	4			1												1		1
7	5						1				1
8	6					1				1						2
9	7								1								1
Mr.Excel New

Example below shows data’s in cells B14:O14 and result match diagonals 2, 3, & 4 is shown in Columns P, Q, & R for match Left Diagonals:<o></o>
<o></o>
And same procedure will be applied to check for right diagonals:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
12	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14	Matched Left Diagonals
13	Game	1	2	3	4	5	6	7	8	9	10	11	12	13	14	2	3	4
14	1	1		1			1	1			1		1		1
15	2		1			1			1	1						2
16	3			1	1				1							1	1
17	4			1	1											1	1	1
18	5						1				1
19	6					1				1
20	7								1
Mr.Excel New

Hope I have made it bit clear to understand well.

This is only an example, as my columns reduces or extends or I want a result anywhere else in the worksheet then I have to change the formula to adapt the ranges.</SPAN></SPAN>

But maybe with UDF I don’t need to do this because it will be easy to re arrange anywhere in the sheet. </SPAN></SPAN>

Thanks and regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
</SPAN>

</SPAN>

 

[motilulla]

Bmpu

 

[Jerry Sullivan]

Here's a formula approach. Paste the 3 formulas into P4,Q4,R4 then copy down;
and Paste the 3 formulas into P15,Q15,R15 then copy down.
The value in Q6 is different than your example, but I think you just missed counting that one.

Excel Workbook
	P	Q	R
1	Matched Right Diagonals
2	2	3	4
3
4	3	0	0
5	2	2	0
6	1	1	1
7	0	0	0
8	2	0	0
9	1	1	0
10
11
12	Matched Left Diagonals
13	2	3	4
14
15	2	0	0
16	1	1	0
17	1	1	1
18	0	0	0
19	0	0	0
20	0	0	0
Sheet

These formulas use a combination of Absolute and Relative references that should allow you to copy-paste columns P:Q to other columns. To resize your game range and keep the formulas working correctly, you'll need to follow a few rules.

To delete entire Rows: Delete from the bottom of the game range
To delete entire Columns: Delete from the middle columns of the game range
To insert entire Rows: Add to the bottom of the game range, the copy the formulas down
To insert entire Columns: Insert from the middle columns of the game range

These limitiations could be reduced with Offset and Index functions but that will make the formulas more complex.

A UDF could simplify the resizing of the game range.

I'm wrapping up for the night, but I'll be glad to write a UDF example tomorrow.

 

[motilulla]

JS411, </SPAN></SPAN>

I am amazed that all the formulas are working perfectly. You pointed out correctly my mistake in Q6 because my counting was done manually. </SPAN></SPAN>

Thank you for explaining how can I extend or reduce the ranges in the formula and really it is great help from your side. </SPAN></SPAN>

I appreciate you very much as you are keen to find me even an UDF example.</SPAN></SPAN>

Thanks and Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Jerry Sullivan]

Moti, Here's a UDF for you to try.

Paste this code into a Standard Code Module in your workbook.

Public Function Score(rGameRange As Range, sDiagonal As String, _
        lMatches As Long)
'---scores one row of game board where:
    '  rGameRange is the entire board
    '  sDiagonal is string that starts with "L" or "R"
    '  lMatches is the number of consecutive 1's to match
    '  the row scored is the row that the Score() function is entered
    '  Example: =Score(B3:N8,"Right",2)

    
    Dim rScoreRange As Range, vSubBoard As Variant
    Dim lRow As Long, lCol As Long, lDirection As Long
    Dim i As Long, lFirstCol As Long, lLastCol As Long

    
    '--handle top rows that can't meet match criteria
    If rGameRange.Row + lMatches - 1 > Application.Caller.Row Then _
        Exit Function

    
    '--read range of interest into array
    Set rScoreRange = Intersect(rGameRange, Application.Caller.EntireRow)
    If rScoreRange Is Nothing Then GoTo ReturnError

    
    vSubBoard = rScoreRange.Resize(lMatches).Offset(1 - lMatches).Value

    
    '--set parameters based on direction of diagonal
    Select Case UCase(Left(sDiagonal, 1))
        Case "L"
            lDirection = -1
            lFirstCol = lMatches
            lLastCol = UBound(vSubBoard, 2)
        Case "R"
            lDirection = 1
            lFirstCol = 1
            lLastCol = UBound(vSubBoard, 2) - lMatches + 1
        Case Else
            GoTo ReturnError
    End Select

    
    '--count matches
    For lCol = lFirstCol To lLastCol
        i = 0
        Do
            If vSubBoard(lMatches - i, lCol + i * lDirection) _
                <> 1 Then Exit Do
            i = i + 1
        Loop While i < lMatches
        If i = lMatches Then Score = Score + 1
    Next lCol

    Exit Function
ReturnError:
    Score = CVErr(xlErrRef)
End Function

Some example formulas:
=Score(B3:N8,"Right",2) ->In AA7: will count Right Diagonals, 2 Matches in B7:N7
=Score(B3:N8,"L",3) -> In AA7: will count Left Diagonals, 3 Matches in B7:N7

To make it easy to resize your game ranges, you could define named Ranges like "GameRange1" Refers to: B3:N8 then enter the formula:
=Score(GameRange1,"R",4)

 

[motilulla]

JS411, </SPAN></SPAN>

I can say that it is a dream come true. </SPAN></SPAN>

Heartily thank you for your help and spending your precious time for giving me a fine solution. It is wonderful and so simple to use. </SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>