Count the number of times a text pattern is used

[pukks]

Hello Forum

I have a sample spreadsheet which I have simplified into two columns. I need to count the number of times a pattern occurs in column B.
So from row 2 to row 8 there is a pattern of text C040015,GAXXXX,JABC,JAFA,JALA,JALB,JALC - this repeats itself again row 9-15 and 16 - 22. (total 3 times)

rows 23 - 28 , 29 - 34 is another pattern C040015,GAXXXX,JABC,JAIA,JALA,JALC occurring twice

rows 35 - 42 , 43 - 50 another pattern JAAA,JAEA,JAFA,JAHC,JAHD,JALA,JALB,JALC occurring twice

so for this spreadsheet

pattern 1 occurs 3 times
pattern 1 occurs 2 times
pattern 1 occurs 2 times

You will note that the only time the pattern can change is when the number changes in column A

thanks
David

[ragulduy]

I think if you run a macro like this:

Please Login or Register  to view this content.

on your example it will put the results you require in columns D and E.

[pukks]

Hi

It works but produces duplicate values as such
,C040015,GAXXXX,JABC,JAFA,JALA,JALB,JALC 3
,C040015,GAXXXX,JABC,JAFA,JALA,JALB,JALC 3
,C040015,GAXXXX,JABC,JAFA,JALA,JALB,JALC 3
,C040015,GAXXXX,JABC,JAIA,JALA,JALC 1
,C040015,GAXXXX,JAFA,JALA,JALB,JALC 1
,JAAA,JAEA,JAFA,JAHC,JAHD,JALA,JALB,JALC 2
,JAAA,JAEA,JAFA,JAHC,JAHD,JALA,JALB,JALC 2


thanks
David

[ragulduy]

Please Login or Register  to view this content.

[pukks]

Hi

I still get the same result although this time a vb error "Invalid call procedure or argument"

thanks
David

[pukks]

Sorry forgot to mention, the final spreadsheet has 12272 rows on it.
David

[ragulduy]

Hi, I edited the code to fix that soon after posting, try it again now and it should be fine.

[newdoverman]

I constructed a chart with the keys in the left column and the patterns as column headers with the totals at the bottom.

[pukks]

Hi, I edited the code to fix that soon after posting, try it again now and it should be fine.

Hi

For this I get subscript out of range.
thanks
David

[ragulduy]

I've just double checked on your example and it runs ok - see attachment with sheet1 as your original data and sheet2 after running the code in module 1.

[pukks]

Yes, I get your example to work, but when I amended the code for extra lines, I get error 400. I have attached the finished spreadsheet.

thanks
David

[ragulduy]

Hi, there was a problem with the formula to get the number of unique lines (the evaluate bit), it was returning .999999 instead of an integer. If you add a round function it should be ok:

Please Login or Register  to view this content.

[ragulduy]

Sorry, I just noticed in your main examples the patterns do not appear consecutively as they did in their example. You also need to sort the results before going through and deleting duplicates:

Please Login or Register  to view this content.

[pukks]

Many thanks for your help.
Issue solved