How to code excel to identify the count for a pattern?

[edlim85]

Hi there,

Say i have a random number pattern...18, 22, 36, 55, 99, 18, 22, 36, 55, 99
Is there a way to code excel to count the series of pattern before it repeats?

So in the above example the count will give me 5. 5 numbers then repeat.

Pls advise


Regards
Edmund

 

[Gerald Higgins]

That's an interesting question.

If your data is always in the format you've described, then yes, it is fairly straighforward to do this.
For example, you could ask Excel to find out how many cells there were between the first cell, and the next cell that contains the same value.

But I'm guessing that maybe your data is really a bit more complicated.

Can you give us more information please ?

For example, could you have data like this
18...22...36...55...99...04...18...22...36...55...99..12...18...22...36
and so on. If you could, is the answer still 5, or something else ?

 

[edlim85]

Hi Gerald Higgins,

Thanks for replying I think I understand your solution. I'm looking at numbers pattern in a straight order. Ie my series will not have numbers that are not in the pattern occuring as shown in ur example.

Thanks!

Sincerely
Edmund

 

[Gerald Higgins]

OK. So if the problem can be simplified to just count how many cells there are between the first cell, and the next cell containing the same value as the first cell (without doing any analysis of the other numbers) then this will work.

This assumes the range of numbers is stored in cells A1:A100.

=MATCH(A1,A2:A100,0)

This returns 5 for your data.

 

[edlim85]

hi there,


In VBA code, do i use Find to look for the next match and return me to row number. With the row number, i subtract from start row to give me the pattern count.

haha. im new here. pls advise. thanks

'Next item row number
Nitem = w1.Range(w1.Cells(4, 8), w1.Cells(LR, 8)).Find(w1.Cells(4, 8), After:=w1.Cells(4, 8), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
'pattern count = Next item row number - start row
itemTot = Nitem - 4