Count diagonal

[Kishan]

Using Excel 2000

Hi,

I am looking VBA that can count 3 diagonals signs "1X2" if the are same then result = 1, if 2 are same 1 is different then result = 2, if 3 are different then result = 3

Step1-Start looking diagonal "1X2" signs in C6, D7, and E8 Find 1, 1, 2 Result 2 in T9
Step2-Start looking diagonal "1X2" signs in D6, E7, and F8 Find 1, X, X Result 2 in U9
Step3-Start looking diagonal "1X2" signs in E6, F7, and G8 Find X, X, 1 Result 2 in V9
Step4-Start looking diagonal "1X2" signs in F6, G7, and H8 Find 1, 1, 1 Result 1 in W9
Check row6 up to N6, O7 and P9

Next step start row 7, 8, 9 and place results in T10:AE10

Note: colour is just to show example clearer...

Example...

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3
4
5		C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14		C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14
6		1	1	X	1	2	1	1	X	1	1	1	2	2	1
7		2	1	X	X	1	1	2	2	X	1	2	1	1	1
8		2	X	2	X	1	1	2	X	X	2	1	1	1	X				2	2	2	1	2	3	3	2	1	2	1	3
9		1	1	2	2	X	2	2	1	1	1	1	1	2	1				2	2	1	3	2	2	3	3	3	1	2	1
10		X	1	1	2	2	1	1	X	X	1	X	1	1	X
11		2	1	1	2	X	1	2	X	X	2	1	1	2	1
12		1	X	2	1	2	X	2	1	X	1	2	X	X	2
13		X	2	2	1	2	1	X	1	1	1	1	1	2	X
14		1	2	2	X	2	1	2	1	X	1	2	2	2	X
15		1	1	2	1	1	2	1	1	1	1	1	1	2	1
16
17
18
Sheet1

Thank you in advance

Regards,
Kishan

 

[StephenCrump]

If your diagonals will always be only three cells, you can use the relatively simple formula:

T8: =IF(C6=D7,IF(D7=E8,3,2),IF(OR(C6=E8,D7=E8),2,1))

But if you're going to extend the length of the diagonal (?) I'd be looking to use a smarter and more general formula.

 

[Kishan]

If your diagonals will always be only three cells, you can use the relatively simple formula:

T8: =IF(C6=D7,IF(D7=E8,3,2),IF(OR(C6=E8,D7=E8),2,1))

Hi StephenCrump, thank you very much it is simple but nice formula. To get result as per opening post I did adapted the formula like this way
T8=IF(C6=D7,IF(D7=E8,1,2),IF(OR(C6=E8,D7=E8),2,3))

But if you're going to extend the length of the diagonal (?) I'd be looking to use a smarter and more general formula.

As there are only 3 signs really I have no idea extending the length of the diagonal what results I will get. If you have something in mind you can generalize the formula. I will check it.

Regards,
Kishan