about Matching

  • Thread starter Thread starter Ting Li
  • Start date Start date
T

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 ?
 
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.
 
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 ?
Click to expand...

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
 
Back
Top