Separate counts depending on start patterns

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

Note: Fill colours not required, it is just to shown to explain the example clearer</SPAN></SPAN>

I want a Formula or batter will be the VBA solution to " Separate counts depending on start patterns ", data in column C:D, count result in columns L:Q & in the S:X</SPAN></SPAN>

Count examples of the row 6, starting C6 has 1|X, so count instance and put them in cell L6 = 2 (Not in the S6), then look count for reverse match pattern X|1=1 put them in cell M6 =1 (not in the T6) then E3= 1|2 put them in N6 (Not in the V6)</SPAN></SPAN>

So the count must be as per left to right order and match put in to L:Q & S:X as per order too. </SPAN></SPAN>

Result data example</SPAN></SPAN>

Book1
	A	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
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7			1|X	X|1	1|2	2|1	X|2	2|X		X|1	1|X	2|1	1|2	2|X	X|2
6			1|X	X|1	1|2	1|1	2|2	1|X	1|1			2	1	1
7			X|1	X|1	X|1	1|X	1|1	1|X	X|1										4	2
8			1|X	1|2	2|1	X|1	1|X	1|2	2|1			2	1	2	2
9			2|X	1|1	1|2	X|X	X|1	1|1	2|1					1	1				1				1
10			1|X	2|2	2|1	X|X	2|2	X|X	1|1			1									1
11			1|X	1|1	X|X	2|2	X|X	1|1	X|X			1
12			X|X	X|1	1|1	1|X	1|X	2|1	2|X						1				1	2			1
13			1|X	X|1	2|1	2|1	1|1	2|X	2|X			1	1		2								2
14			X|2	X|1	1|X	1|1	1|1	X|1	1|X							1			2	2
15			1|1	1|1	1|1	X|X	X|1	1|1	2|X				1										1
16			1|2	1|2	X|1	1|2	X|2	1|1	1|X					3		1			1	1
17			1|X	X|1	1|2	X|X	1|X	1|1	X|1			2	2	1
18			1|1	1|X	1|1	1|1	X|1	1|1	1|1			1	1
19			X|1	2|X	1|1	1|2	X|1	1|2	X|X				2	2									1
20			1|1	1|X	1|X	X|1	X|1	1|1	1|1			2	2
21			1|1	1|1	1|X	X|2	1|1	1|1	1|2			1		1		1
22			1|1	1|1	1|1	1|1	1|1	2|X	X|1										1				1
23			1|2	2|1	1|1	1|1	1|1	1|X	1|1			1		1	1
24			1|1	X|1	1|1	1|1	1|1	1|1	2|1										1		1
25			X|X	X|1	1|1	X|1	1|2	X|1	1|1				3	1
26			X|2	1|1	2|1	1|1	1|1	X|X	1|X			1				1					1
27			1|X	2|2	1|1	1|2	X|X	1|1	1|X			2		1
28			2|1	X|X	1|1	X|X	1|X	1|1	1|1			1									1
29			X|1	2|1	2|X	X|1	1|2	X|1	1|2										3		1	2	1
30			X|1	X|X	1|1	1|1	1|1	1|X	1|1										1	1
31
32
Sheet7

Thank you in advance</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Fluff]

In L6
=COUNTIF($C6:$I6,L$5)
then copy across & down

 

[Kishan]

In L6
=COUNTIF($C6:$I6,L$5)
then copy across & down

Hi Fluff, it is a general formula that does not look the order see the result are not exactly what I want.</SPAN></SPAN>

Book1
	A	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
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7			1|X	X|1	1|2	2|1	X|2	2|X		X|1	1|X	2|1	1|2	2|X	X|2
6			1|X	X|1	1|2	1|1	2|2	1|X	1|1			2	1	1					1	2		1
7			X|1	X|1	X|1	1|X	1|1	1|X	X|1			2	4						4	2
8			1|X	1|2	2|1	X|1	1|X	1|2	2|1			2	1	2	2				1	2	2	2
9			2|X	1|1	1|2	X|X	X|1	1|1	2|1				1	1	1		1		1		1	1	1
10			1|X	2|2	2|1	X|X	2|2	X|X	1|1			1			1					1	1
11			1|X	1|1	X|X	2|2	X|X	1|1	X|X			1								1
12			X|X	X|1	1|1	1|X	1|X	2|1	2|X			2	1		1		1		1	2	1		1
13			1|X	X|1	2|1	2|1	1|1	2|X	2|X			1	1		2		2		1	1	2		2
14			X|2	X|1	1|X	1|1	1|1	X|1	1|X			2	2			1			2	2				1
15			1|1	1|1	1|1	X|X	X|1	1|1	2|X				1				1		1				1
16			1|2	1|2	X|1	1|2	X|2	1|1	1|X			1	1	3		1			1	1		3		1
17			1|X	X|1	1|2	X|X	1|X	1|1	X|1			2	2	1					2	2		1
18			1|1	1|X	1|1	1|1	X|1	1|1	1|1			1	1						1	1
19			X|1	2|X	1|1	1|2	X|1	1|2	X|X				2	2			1		2			2	1
20			1|1	1|X	1|X	X|1	X|1	1|1	1|1			2	2						2	2
21			1|1	1|1	1|X	X|2	1|1	1|1	1|2			1		1		1				1		1		1
22			1|1	1|1	1|1	1|1	1|1	2|X	X|1				1				1		1				1
23			1|2	2|1	1|1	1|1	1|1	1|X	1|1			1		1	1					1	1	1
24			1|1	X|1	1|1	1|1	1|1	1|1	2|1				1		1				1		1
25			X|X	X|1	1|1	X|1	1|2	X|1	1|1				3	1					3			1
26			X|2	1|1	2|1	1|1	1|1	X|X	1|X			1			1	1				1	1			1
27			1|X	2|2	1|1	1|2	X|X	1|1	1|X			2		1						2		1
28			2|1	X|X	1|1	X|X	1|X	1|1	1|1			1			1					1	1
29			X|1	2|1	2|X	X|1	1|2	X|1	1|2				3	2	1		1		3		1	2	1
30			X|1	X|X	1|1	1|1	1|1	1|X	1|1			1	1						1	1
31
32
Sheet7-1

These count are bit tricky, I have separated them in 2 groups one in the column L:Q & other in the column S:X, here I give you an example of pattern in cells L5: L6 are 1|X, X|1 and in the cells S5:T5 are in reverse order X|1, 1|X, so far count trick is if starting of the row it is 1|X than count will go in L:M, or if starting of the row it is X|1 than count will go in S:T that is why row 6 count in L6:M6 and Row 7 count are in S6:T6</SPAN></SPAN>

Hope this helps</SPAN></SPAN>

Thank you</SPAN></SPAN>


Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Fluff]

I had not fully understood your requirement and unfortunately, cannot figure out how to do it.

 

[Rick Rothstein]

These count are bit tricky, I have separated them in 2 groups one in the column L:Q & other in the column S:X, here I give you an example of pattern in cells L5: L6 are 1|X, X|1 and in the cells S5:T5 are in reverse order X|1, 1|X, so far count trick is if starting of the row it is 1|X than count will go in L:M, or if starting of the row it is X|1 than count will go in S:T that is why row 6 count in L6:M6 and Row 7 count are in S6:T6

I am having trouble deciphering your count requirement. Can you explain why the counts for Rows 12, 26, 28 are in the columns they are in?

 

[Kishan]

I had not fully understood your requirement and unfortunately, cannot figure out how to do it.

Hi Fluff, in the data column C:I, there are total 9 unique patterns, we can put them in 4 different groups as shown below </SPAN></SPAN>
Group1 - 1|1, X|X, 2,2</SPAN></SPAN>
Group2 - 1|X, X|1</SPAN></SPAN>
Group3 - 1|2, 2|1
</SPAN></SPAN>Group4 - X|2, 2|X </SPAN></SPAN>

Group1 - has only one order, whereever the can appear in the row there is no reverse match. It will look always like the same 1|1, X|X, 2|2 the can not be reversed</SPAN></SPAN>

Group2- can be appear in the row as it original order first 1|X and after X|1, or May it can come first X|1 and after 1|X</SPAN></SPAN></SPAN>

Group3- can be appear in the row as it original order first 1|2 and after 2|1, or May it can come first 2|1 and after 1|2</SPAN></SPAN></SPAN>

Group4- can be appear in the row as it original order first X|2 and after 2|X, or May it can come first 2|X and after X|2</SPAN></SPAN>
So what I did group 2, 3 & 4 I have distributed in 2 groups1st-in the original order and 2nd-in their reverse order as they can be appeared </SPAN></SPAN>

Original order - 1|X, X|1, 1|2, 2|1, X|2, 2|X------ Reverse order - X|1, 1|X, 2|1, 1|2, X|2, 2|X</SPAN></SPAN>

If the row 6 has following patterns Post#1</SPAN></SPAN>
[TABLE="width: 252"]
<TBODY>[TR]
[TD]1|X</SPAN>
[/TD]
[TD]X|1</SPAN>
[/TD]
[TD]1|2</SPAN>
[/TD]
[TD]1|1</SPAN>
[/TD]
[TD]2|2</SPAN>
[/TD]
[TD]1|X</SPAN>
[/TD]
[TD]1|1</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
In this above line 1|X is appeared in the starting first and followed by X|1 so count will go in the group Original order 1|X = 2 (in the cell L6) and X|1=1 (in the cell M6)</SPAN></SPAN>

If the row 7 has following patterns Post#1</SPAN></SPAN>
[TABLE="width: 252"]
<TBODY>[TR]
[TD]X|1</SPAN>
[/TD]
[TD]X|1</SPAN>
[/TD]
[TD]X|1</SPAN>
[/TD]
[TD]1|X</SPAN>
[/TD]
[TD]1|1</SPAN>
[/TD]
[TD]1|X</SPAN>
[/TD]
[TD]X|1</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
In this above line X|1 is appeared in the starting first and followed by 1|X so count will go in the group Reverse order X|1 = 4 (in the cell S6) and 1|X=2 (in the cell T6)</SPAN></SPAN>

The same method will be applied for the rest rows </SPAN></SPAN>
Hope this helps</SPAN></SPAN>

Thank you</SPAN></SPAN>


Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Kishan]

I am having trouble deciphering your count requirement. Can you explain why the counts for Rows 12, 26, 28 are in the columns they are in?

Hi Rick Rothstein, I think I have wrong placed in the row 12, but the row 26 & 28 seem to be ok</SPAN></SPAN>

Post#1 as shown</SPAN></SPAN>

Book1
	A	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
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7			1|X	X|1	1|2	2|1	X|2	2|X		X|1	1|X	2|1	1|2	2|X	X|2
12			X|X	X|1	1|1	1|X	1|X	2|1	2|X						1				1	2			1
26			X|2	1|1	2|1	1|1	1|1	X|X	1|X			1				1					1
28			2|1	X|X	1|1	X|X	1|X	1|1	1|1			1									1
31
32
Sheet7

It should be as below</SPAN></SPAN>

Book1
	A	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
1
2
3
4
5			P1	P2	P3	P4	P5	P6	P7			1|X	X|1	1|2	2|1	X|2	2|X		X|1	1|X	2|1	1|2	2|X	X|2
12			X|X	X|1	1|1	1|X	1|X	2|1	2|X										1	2	1		1
26			X|2	1|1	2|1	1|1	1|1	X|X	1|X			1				1					1
28			2|1	X|X	1|1	X|X	1|X	1|1	1|1			1									1
31
32
Sheet7-2

Hope this helps</SPAN></SPAN>

Thank you</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Rick Rothstein]

Hi Rick Rothstein, I think I have wrong placed in the row 12, but the row 26 & 28 seem to be ok

I think I may be starting to understand what you want. Your answer to the following question will tell me if I am right or not. For your example, is the green count in the wrong group for Row 13?

 

[Kishan]

I think I may be starting to understand what you want. Your answer to the following question will tell me if I am right or not. For your example, is the green count in the wrong group for Row 13?

Hi Rick Rothstein, yes I am sorry, Row 13 green count are placed in the wrong group, count should be in other group in the cell U13, but they are placed wrongly in the wrong group in the cell O13</SPAN></SPAN>

Thank you for observing it minutely </SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[TD="bgcolor: #DCE6F1"]

Q​

[/TD]
[TD="bgcolor: #DCE6F1"]

R​

[/TD]
[TD="bgcolor: #DCE6F1"]

S​

[/TD]
[TD="bgcolor: #DCE6F1"]

T​

[/TD]
[TD="bgcolor: #DCE6F1"]

U​

[/TD]
[TD="bgcolor: #DCE6F1"]

V​

[/TD]
[TD="bgcolor: #DCE6F1"]

W​

[/TD]
[TD="bgcolor: #DCE6F1"]

X​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

P1​

[/TD]
[TD]

P2​

[/TD]
[TD]

P3​

[/TD]
[TD]

P4​

[/TD]
[TD]

P5​

[/TD]
[TD]

P6​

[/TD]
[TD]

P7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

2|1​

[/TD]
[TD]

X|2​

[/TD]
[TD]

2|X​

[/TD]
[TD][/TD]
[TD]

X|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

2|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

2|X​

[/TD]
[TD]

X|2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|2​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

X|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

1|2​

[/TD]
[TD]

2|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|2​

[/TD]
[TD]

2|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

2|2​

[/TD]
[TD]

2|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

2|2​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

2|2​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|X​

[/TD]
[TD]

2|1​

[/TD]
[TD]

2|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

2|1​

[/TD]
[TD]

2|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|X​

[/TD]
[TD]

2|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|2​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|2​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|2​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|1​

[/TD]
[TD]

2|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

X|2​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|X​

[/TD]
[TD]

X|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|2​

[/TD]
[TD]

2|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|2​

[/TD]
[TD]

1|1​

[/TD]
[TD]

2|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1|X​

[/TD]
[TD]

2|2​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

2|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

29​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|1​

[/TD]
[TD]

2|1​

[/TD]
[TD]

2|X​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD]

X|1​

[/TD]
[TD]

1|2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

30​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

X|1​

[/TD]
[TD]

X|X​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|1​

[/TD]
[TD]

1|X​

[/TD]
[TD]

1|1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

31​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in L6 copied across until Q6 and down
=IF(COUNTIF($C6:$I6,L$5),IF(MOD(COLUMN(L6),2)=0,IF(MATCH(L$5,$C6:$I6,0)>IFERROR(MATCH(RIGHT(L$5)&"|"&LEFT(L$5),$C6:$I6,0),100),"",COUNTIF($C6:$I6,L$5)),IF(AND(COUNTIF($C6:$I6,L$5),K6<>""),COUNTIF($C6:$I6,L$5),"")),"")

Formula in S6 copied across until X6 and down
=IF(AND(COUNTIF($C6:$I6,S$5),INDEX($L6:$Q6,MATCH(S$5,$L$5:$Q$5,0))=""),COUNTIF($C6:$I6,S$5),"")

Hope this helps

M.