about Matching

[Ting Li]

Hi everyone
I have got a column of data look like belows:
A
B
C
D
B
C
A
I want to find out the frequecy of certain consecutive patterns and generate
outputs like:
BC 2
BCD 1
BCA 1
ABCD 1

Which function(s) in Excel I can make us of ?

 

[T. Valko]

Try these:

For BC or any 2 letter combination:

=SUMPRODUCT(--(A1:A6="B"),--(A2:A7="C"))

For BCD or any 3 letter combination:

=SUMPRODUCT(--(A1:A5="B"),--(A2:A6="C"),--(A3:A7="D"))

For ABCD or any 4 letter combination:

=SUMPRODUCT(--(A1:A4="A"),--(A2:A5="B"),--(A3:A6="C"),--(A4:A7="D"))

Note the progressive offsets of the ranges based on the number of letters
being counted.

 

[Ron Rosenfeld]

Hi everyone
I have got a column of data look like belows:
A
B
C
D
B
C
A
I want to find out the frequecy of certain consecutive patterns and generate
outputs like:
BC 2
BCD 1
BCA 1
ABCD 1

Which function(s) in Excel I can make us of ?

If your data is in A1:A7, and the pattern(s) you are looking for is in B1:Bn
you could use this formula:


=(SUM(LEN(MID(CONCATENATE($A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7),
ROW(INDIRECT("1:"&LEN(CONCATENATE($A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7))
+1-LEN(B1))),LEN(B1))))-SUM(LEN(SUBSTITUTE(MID(CONCATENATE(
$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7),ROW(INDIRECT("1:"&LEN(CONCATENATE(
$A$1,$A$2,$A$3,$A$4,$A$5,$A$6,$A$7))+1-LEN(B1))),LEN(B1)),B1,""))))/LEN(B1)

-------------------------

If your data is longer than 29 cells (pre-Excel 2003) or 255 cells (Excel
2007), then you can use the ampersand to construct the string.

If you fill down that formula, the reference to the pattern will change
appropriately.

-------------------------------------

If your data will be less than 256 characters, you could also download and
install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/index.htm

and use this simpler formula:

=REGEX.COUNT(MCONCAT($A$1:$A$7),B1)

Again, it assumes your data is in A1:A7, and the patterns are in B1:Bn.
--ron

 

[Ting Li]

Thank you so much.
It works the way i want.
Could you please explain a little bit more on the use of "--" that shown in
your function?
I would like to know more about the excel language.
Thanks

 

[T. Valko]

See these:

http://mcgimpsey.com/excel/formulae/doubleneg.html

This linked is temporarily "dead" but should be ok at the beginning of the
next month:

http://xldynamic.com/source/xld.SUMPRODUCT.html

It has an extensive discussion about SUMPRODUCT including the "--".