Counting Pattern Occurences in Multiple Rows

  • Thread starter Thread starter Lucy
  • Start date Start date
L

Lucy

Hi there!

I have a worksheet that has many rows of data, 6 columns
each. The data looks something like this:

Row1: 0, 2, 2, 2, 4, 5
Row2: 1, 1, 2, 3, 3, 4
Row3: 1, 1, 2, 3, 3, 4

I would like to count how many times a specific pattern
comes up. For example, how many times does 1,1,1,2,5 come
up in the list? Any help would be greatly appreciated!!!

Me

P.S. The more complex problem (if I can do what is
mentioned above) is to find a way for Excel to tell me how
many combinations there are AND a count for each. That
would really be fantastic...
 
CORRECTION:

....how many times does 1,1,1,2,4,5 come up...

(the pattern searched for always has 6 values - 1 for each
column)

Thank you :)
 
Perhaps this set-up might be of some use:

Assume your data is in cols A to F, row1 down

Insert a new row1

Put in G1: Combo (just a label)
Put in G2: =A2&B2&C2&D2&E2&F2
Copy G2 down as many rows as you have data

Select col G
Click Data > Filter > Advanced Filter
[Click OK to the Excel prompt to use the first row as labels]

In the Advanced Filter dialog box:
----------------------------------------
Check "Copy to another location"
Put for "Copy to:" : H1
Check "Unique records only"
Click OK

The unique list of combo items in col G
will be extracted in col H

Put in say L1: =COUNTA(H:H)-1
This gives the count of unique items in col H
(other than the col header)

--
Put in I2: =COUNTIF(G:G,H2)
Put in J2: =I2+ROW()*0.0000001
Put in K2
=OFFSET($A$1,MATCH(LARGE(J:J,ROW()-1),J:J,0)-1,7)

Select I2:K2 and copy down
as many rows as there is data in col H

Col I returns the frequency count for
the corresponding combo item in col H

Col K returns all the combos in col H
sorted in the descending order
(highest frequency down)

Col J is used to "separate" combos with duplicate counts in col I,
as there probably would be a number of ties happening
for the counts in col I.
 
Back
Top