Exact Match

[cotech_10]

Hi All,


I am trying to construct a formula that will allow me to compare two or more cells of data against any given range of secondary data that have the exact
Data Sequence.

The table "Data Range 1" contains the set of criteria data, in this example
there are (4) Configurations to be analysed:

Config: 1 ; Config: 2 ; Config: 3 ; Config 4

The table "Data Range 2" contains the range of data to
be used against the criteria data.


The Table "Output" contains the results of the analysis of the (3) Configurations


In this example Config 1 has a count of "4". There are (4) instances in Table 2 where
there is an EXACT match with the same sequence patetrn of "A and H"

The same applies to Config 2,3 & 4

Config 2 has a count of 0 as there are no matches
Config 3 has a count of 2 as there 2 instances where
the sequence pattern of B,C,A,A,A exactly matches.
Config 4 also has a count of 2 as there 2 instances where
the sequence pattern of F,G,A,A,A exactly matches.



Cheers

 

[DanteAmor]

These would be some formulas to find the configurations:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		Config 1	A		Config 2	B		Config 3	B		Config 4	F
4			H			C			C			G
5									A			A
6									A			A
7									A			A
8
9
10
11
12		A	B	D	F	A	H	A	A	H	A	B	B	C	D	F	F
13		H	C	E	G	H	A	H	H	H	A	C	C	B	E	G	G
14			A									A	A			A	A
15			A									B	A			A	A
16			A									B	A			A	A
17
18
19
20
21
22
23								Count
24						Config 1		4
25						Config 2		0
26						Config 3		2
27						Config 4		2
Hoja1

Cell Formulas
Range	Formula
H24	H24	=SUMPRODUCT(($B$12:$Q$12=C$3)*($B$13:$Q$13=C$4)*($B$14:$Q$14=C$5)*($B$15:$Q$15=C$6)*($B$16:$Q$16=C$7))
H25	H25	=SUMPRODUCT(($B$12:$Q$12=F$3)*($B$13:$Q$13=F$4)*($B$14:$Q$14=F$5)*($B$15:$Q$15=F$6)*($B$16:$Q$16=F$7))
H26	H26	=SUMPRODUCT(($B$12:$Q$12=I$3)*($B$13:$Q$13=I$4)*($B$14:$Q$14=I$5)*($B$15:$Q$15=I$6)*($B$16:$Q$16=I$7))
H27	H27	=SUMPRODUCT(($B$12:$Q$12=L$3)*($B$13:$Q$13=L$4)*($B$14:$Q$14=L$5)*($B$15:$Q$15=L$6)*($B$16:$Q$16=L$7))

 

[cotech_10]

Hi DanteAmor,

Thak you for your response, much appreciated. I have copied the formulas into my spreadsheet and so far it seems
to be working the way I designed it to do..

Will confirm with a bit more testing on additional Data .!!!!!




Cheers

 

[cotech_10]

Hi DanteAmor,

I did additional testing and the solution you have provided works well...

Thanking you once again

Cheers

 

[Peter_SSs]

COUNTIFS would be another option

23 11 20.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Config 1	A		Config 2	B		Config 3	B		Config 4	F
4		H			C			C			G
5								A			A
6								A			A
7								A			A
8
11
12	A	B	D	F	A	H	A	A	H	A	B	B	C	D	F	F
13	H	C	E	G	H	A	H	H	H	A	C	C	B	E	G	G
14		A									A	A			A	A
15		A									B	A			A	A
16		A									B	A			A	A
17
23							Count
24					Config 1		4
25					Config 2		0
26					Config 3		2
27					Config 4		2
COUNTIFS

Cell Formulas
Range	Formula
H24	H24	=COUNTIFS(B12:Q12,T(C3),B13:Q13,T(C4),B14:Q14,T(C5),B15:Q15,T(C6),B16:Q16,T(C7))
H25	H25	=COUNTIFS(B12:Q12,T(F3),B13:Q13,T(F4),B14:Q14,T(F5),B15:Q15,T(F6),B16:Q16,T(F7))
H26	H26	=COUNTIFS(B12:Q12,T(I3),B13:Q13,T(I4),B14:Q14,T(I5),B15:Q15,T(I6),B16:Q16,T(I7))
H27	H27	=COUNTIFS(B12:Q12,T(L3),B13:Q13,T(L4),B14:Q14,T(L5),B15:Q15,T(L6),B16:Q16,T(L7))

 

[cotech_10]

Hi Peter,

Thank you for your contribution as well, much appreciated.

I have tried the solution you have provided I could not get it to work initially but when I removed the references of the letter "T" which precedes
the Range Criteria variables it works.

Cheers

 

[Peter_SSs]

I have tried the solution you have provided I could not get it to work initially but when I removed the references of the letter "T" which precedes
the Range Criteria variables it works.

Sounds like there might possibly be something in some of the cells that we cannot see from your image. All the more reason to use XL2BB if possible next time.

Anyway, glad you have it working now.

 

[cotech_10]

Hi Peter,

Cheers.... will do next time and use XL2BB