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 ?

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.


--
Biff
Microsoft Excel MVP


"Ting Li" wrote in message
...
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 ?

On Fri, 29 Aug 2008 19:36:00 -0700, Ting Li
wrote:

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

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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Ting Li" wrote in message
...
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 ?

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 "--".

--
Biff
Microsoft Excel MVP


"Ting Li" wrote in message
...
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" wrote:

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.


--
Biff
Microsoft Excel MVP


"Ting Li" wrote in message
...
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 ?