Valid Matching Outcomes

[cotech_10]

I would like to request the following task possibly to be done using excel formulas:

I have 7 possible numbers in Table 1 and would like to do a comparison analysis
with the numbers in Table 2. As per the image below:

In Table 2 there are 12 positions I would like to perform alternate analysis
based on Positions.

Step 1:

I would like to compare Positions 1,3,5,7,9,11 with all of the number values in Table 1
and count the number of match occurrences with those numbers.

For eg: From Table 2 the following Position Number values are:

Position 1=200
Position 3=330
Position 5=440
Position 7=520
Position 9=620
Position 11=730

According to Match Rule for these Positions we are only concerned with the outcomes where there is a valid
match outcome.

Position 1=200 MATCHES with one of the numbers in TABLE 1 which is a valid outcome
Position 3=330 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 5=440 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 7=520 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 9=620 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 11=730 does not match with any of the numbers in TABLE 1 which is not a valid outcome

The only match value is Position 1

Table 1 = 200 Table 2 = 200

Therefore the match count = 1

STEP 2:

The second part of the calculation and to be included in the Total Count is as follows:

I would like to compare Positions 2,4,6,8,10,12 with all of the number values in Table 1
and count the number of NON-MATCH occurrences with those numbers.

For eg: From Table 2 the following Position Number values are:


Position 2=240
Position 4=350
Position 6=450
Position 8=550
Position 10=650
Position 12=800


According to Match Rule we are only concerned with the outcomes where there is a valid
NON-MATCH outcome.


For eg:

Position 2=240 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 4=350 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 6=450 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 8=550 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 10=650 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 12=800 does MATCH with one of the numbers in TABLE 1 which is a non-valid outcome


The Matching Outcomes are Positions 2,4,6,8 & 10 and NOT Position 12 as the rule
specifies that non-matching conditions are to be met.


The number of outcomes that satisfy this rule = 5


The Totals for these (2) levels of analysis is 6.

1. STEP 1 = 1
STEP 2 = 5

Total = 6 which is the value placed in the OUTPUT Table


I look forward in hearing back from someone soon



Regards

 

[Peter_SSs]

Like this?

24 01 02.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	200	300	400	500	600	700	800
3
4
5	Position 1	Position 2	Position 3	Position 4	Position 5	Position 6	Position 7	Position 8	Position 9	Position 10	Position 11	Position 12		Count
6	200	240	330	350	440	450	520	550	620	650	730	800		6
7	199	200	299	300	399	400	499	500	599	600	699	700		0
8	200	299	300	399	400	499	500	599	600	699	700	799		12
Count

Cell Formulas
Range	Formula
O6:O8	O6	=SUMPRODUCT(ISODD(RIGHT(B$5:M$5,2)+0)*ISNUMBER(MATCH(B6:M6,B$2:H$2,0)))+SUMPRODUCT(ISEVEN(RIGHT(B$5:M$5,2)+0)*ISNA(MATCH(B6:M6,B$2:H$2,0)))

 

[cotech_10]

24 01 02.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	200	300	400	500	600	700	800
3
4
5	Position 1	Position 2	Position 3	Position 4	Position 5	Position 6	Position 7	Position 8	Position 9	Position 10	Position 11	Position 12		Count
6	200	240	330	350	440	450	520	550	620	650	730	800		6
7	199	200	299	300	399	400	499	500	599	600	699	700		0
8	200	299	300	399	400	499	500	599	600	699	700	799		12
Count

Cell Formulas
Range	Formula
O6:O8	O6	=SUMPRODUCT(ISODD(RIGHT(B$5:M$5,2)+0)*ISNUMBER(MATCH(B6:M6,B$2:H$2,0)))+SUMPRODUCT(ISEVEN(RIGHT(B$5:M$5,2)+0)*ISNA(MATCH(B6:M6,B$2:H$2,0)))

Perfect.... Thanks Peter..!!!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[felixstraube]

Another option:

=SUM((B6:M6*{1,0,1,0,1,0,1,0,1,0,1,0}=TRANSPOSE($B$2:$H$2))*1)+6-SUM((B6:M6*{0,1,0,1,0,1,0,1,0,1,0,1}=TRANSPOSE($B$2:$H$2))*1)

 

[cotech_10]

Another option:

=SUM((B6:M6*{1,0,1,0,1,0,1,0,1,0,1,0}=TRANSPOSE($B$2:$H$2))*1)+6-SUM((B6:M6*{0,1,0,1,0,1,0,1,0,1,0,1}=TRANSPOSE($B$2:$H$2))*1)

Hi Felixstraube,

Thank you for the solution you have provided, always appreciate input from other members.
I used the solution you have provided and I get a return cell error message in the cell where
the formula was copied to.

"#VALUE!"

I am not sure if it is a typing issue or cell referencing.



Cheers

 

[felixstraube]

It is an array formula so for it to work you have to enter it pressing Ctrl+Shift+Enter.

 

[felixstraube]

Or you can try this formula. With this one you can just hit enter (no Ctrl+Shift+Enter required):

=SUMPRODUCT((B6:M6*{1,0,1,0,1,0,1,0,1,0,1,0}=TRANSPOSE($B$2:$H$2))*1)+6-SUMPRODUCT((B6:M6*{0,1,0,1,0,1,0,1,0,1,0,1}=TRANSPOSE($B$2:$H$2))*1)

 

[cotech_10]

Or you can try this formula. With this one you can just hit enter (no Ctrl+Shift+Enter required):

=SUMPRODUCT((B6:M6*{1,0,1,0,1,0,1,0,1,0,1,0}=TRANSPOSE($B$2:$H$2))*1)+6-SUMPRODUCT((B6:M6*{0,1,0,1,0,1,0,1,0,1,0,1}=TRANSPOSE($B$2:$H$2))*1)

Hi..

Thanks for both responses.. I tried the original array formula solution and it works with Ctrl+Shift+Enter

The 2nd solution seems to be an array formula as well as to execute I needed to enter the Ctrl+Shift+Enter


Cheers

 

[felixstraube]

You are welcome. Thanks for the feedback.