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