Hello,</SPAN></SPAN>
Here is an example for rows 3 that will be applied to all data are in C:K</SPAN></SPAN>
For example if 2 consecutive cells has cell M1 values, then find 3rd if it is 5, 9 or M as per M1:O1 and count them (C3&D3=M | 5 so no match 5 | 5, Go To D3&E3= 5 | 5 Check F3=5 (so after 5 | 5 found 5 count 1 result in M2), Go To E3&F3= 5 | 5 Check G3=9 (so after 5 | 5 found 9 count 1 result in N2), DO COUNT ALL SAME WAY...</SPAN></SPAN>
Note: for now I am using formula below but I want could this formula be shorter or any VBA solution (because in this example I have used 9 numbers, in fact I have 18 numbers so in the excel 2000 does not accept too long formula)</SPAN></SPAN>
Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>
Here is an example for rows 3 that will be applied to all data are in C:K</SPAN></SPAN>
For example if 2 consecutive cells has cell M1 values, then find 3rd if it is 5, 9 or M as per M1:O1 and count them (C3&D3=M | 5 so no match 5 | 5, Go To D3&E3= 5 | 5 Check F3=5 (so after 5 | 5 found 5 count 1 result in M2), Go To E3&F3= 5 | 5 Check G3=9 (so after 5 | 5 found 9 count 1 result in N2), DO COUNT ALL SAME WAY...</SPAN></SPAN>
Note: for now I am using formula below but I want could this formula be shorter or any VBA solution (because in this example I have used 9 numbers, in fact I have 18 numbers so in the excel 2000 does not accept too long formula)</SPAN></SPAN>
Book1 | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | 5 | 5 | ||||||||||||||||
2 | n1 | n2 | n3 | n4 | n5 | n6 | n7 | n8 | n9 | 5 | 9 | M | |||||
3 | M | 5 | 5 | 5 | 9 | 5 | 9 | 5 | 9 | 1 | 1 | 0 | |||||
4 | M | M | 9 | 5 | M | M | 5 | 5 | 9 | 0 | 1 | 0 | |||||
5 | M | 5 | 5 | 5 | 9 | 5 | 9 | 5 | 9 | 1 | 1 | 0 | |||||
6 | 9 | 5 | 5 | M | 9 | 5 | M | 5 | 5 | 0 | 0 | 1 | |||||
7 | 5 | M | 5 | M | 5 | 5 | 9 | 5 | 5 | 0 | 1 | 0 | |||||
8 | M | 9 | M | M | 9 | M | 5 | 5 | 9 | 0 | 1 | 0 | |||||
9 | M | 5 | 5 | 9 | 5 | 5 | 5 | 9 | 5 | 1 | 2 | 0 | |||||
10 | 5 | M | M | 9 | 5 | M | 5 | 5 | 5 | 1 | 0 | 0 | |||||
11 | 5 | 5 | 5 | 9 | M | M | M | 5 | M | 1 | 1 | 0 | |||||
12 | 5 | M | M | 5 | 5 | 5 | 5 | 5 | M | 3 | 0 | 1 | |||||
13 | M | 5 | M | 9 | 5 | 9 | M | M | M | 0 | 0 | 0 | |||||
14 | 5 | 5 | M | 5 | 9 | 5 | M | 5 | 5 | 0 | 0 | 1 | |||||
15 | 5 | 5 | 5 | 5 | 5 | M | 5 | 5 | 5 | 4 | 0 | 1 | |||||
16 | M | 5 | 5 | M | M | 5 | 5 | 5 | 5 | 2 | 0 | 1 | |||||
17 | 5 | 5 | 5 | 5 | 9 | 5 | M | M | 5 | 2 | 1 | 0 | |||||
18 | 5 | 5 | 5 | 9 | 9 | 5 | 5 | M | 5 | 1 | 1 | 1 | |||||
19 | 9 | 5 | M | 5 | 5 | M | 5 | 5 | 5 | 1 | 0 | 1 | |||||
20 | 5 | 5 | 5 | 5 | 5 | 9 | 5 | 5 | 9 | 3 | 2 | 0 | |||||
Sheet 9 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M3 | =IF($C3&" | "&$D3=$M$1,IF($E3=M$2,1,0),0)+IF($D3&" | "&$E3=$M$1,IF($F3=M$2,1,0),0)+IF($E3&" | "&$F3=$M$1,IF($G3=M$2,1,0),0)+IF($F3&" | "&$G3=$M$1,IF($H3=M$2,1,0),0)+IF($G3&" | "&$H3=$M$1,IF($I3=M$2,1,0),0)+IF($H3&" | "&$I3=$M$1,IF($J3=M$2,1,0),0)+IF($I3&" | "&$J3=$M$1,IF($K3=M$2,1,0),0) | |
N3 | =IF($C3&" | "&$D3=$M$1,IF($E3=N$2,1,0),0)+IF($D3&" | "&$E3=$M$1,IF($F3=N$2,1,0),0)+IF($E3&" | "&$F3=$M$1,IF($G3=N$2,1,0),0)+IF($F3&" | "&$G3=$M$1,IF($H3=N$2,1,0),0)+IF($G3&" | "&$H3=$M$1,IF($I3=N$2,1,0),0)+IF($H3&" | "&$I3=$M$1,IF($J3=N$2,1,0),0)+IF($I3&" | "&$J3=$M$1,IF($K3=N$2,1,0),0) | |
O3 | =IF($C3&" | "&$D3=$M$1,IF($E3=O$2,1,0),0)+IF($D3&" | "&$E3=$M$1,IF($F3=O$2,1,0),0)+IF($E3&" | "&$F3=$M$1,IF($G3=O$2,1,0),0)+IF($F3&" | "&$G3=$M$1,IF($H3=O$2,1,0),0)+IF($G3&" | "&$H3=$M$1,IF($I3=O$2,1,0),0)+IF($H3&" | "&$I3=$M$1,IF($J3=O$2,1,0),0)+IF($I3&" | "&$J3=$M$1,IF($K3=O$2,1,0),0) |
Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN>
Last edited: