Finding Sequences within a Column

**sammy2xx** · 11-25-2010, 07:58 AM

Hi,

Can anyone tell me how I might by able to find a sequence in within a column of numbers.

For example, if I have the following in cells A1:A10

1
0
0
0
1
1
1
1
0
0

I can see that the maximum sequence of 1's is four. What would be the easisest way to formulate this. Unfortunately I have a lot more than 10 cells to check!

Thanks.

**TMS** · 11-25-2010, 09:01 AM

You got two replies last time you posted this (exact) question.

http://www.excelforum.com/excel-gene...hin-a-row.html

The only difference seems to be the sample numbers.

Did neither of those solutions work for you?

Regards

**Marcol** · 11-25-2010, 09:03 AM

A basic way might be

C2

Please Login or Register  to view this content.

Drag/Fill Down

B2

Please Login or Register  to view this content.

Drag/Fill Down

This should give the sequence of any groups of numbers=

To find the longest run of a number, in this example it will be zeros

Please Login or Register  to view this content.

Confirmed with Ctrl+Shift+Enter
Alter the range limits to suit.

Hope this helps

[EDIT]
Thanks for the warning TMShucks unfortunately to late!
WTH this is a different approach, perhaps not as elegant as some might offer.

**sammy2xx** · 11-25-2010, 09:53 AM

Originally Posted by TMShucks

You got two replies last time you posted this (exact) question.

http://www.excelforum.com/excel-gene...hin-a-row.html

The only difference seems to be the sample numbers.

Did neither of those solutions work for you?

Regards

Appologies - I didn't setup the e-mail notification and couldn't find my original thread to see if anybody had replied.

Thanks very much for your help and sorry for the duplication.