Find Sequences within a Row

Hi,

I'm looking for a formula that will find the longest sequense in a column of numbers. For example:

A1 - A10 are populated with either 1,2 or 3:

1
1
2
3
3
3
3
1
2
2

I can see the longest sequences are:

1 - 2
2 - 2
3 - 4

How would I formulate this, as unfortunately I have more than 10 cells to check.

Originally Posted by sammy2xx

Hi,

I'm looking for a formula that will find the longest sequense in a column of numbers. For example:

A1 - A10 are populated with either 1,2 or 3:

1
1
2
3
3
3
3
1
2
2

I can see the longest sequences are:

1 - 2
2 - 2
3 - 4

How would I formulate this, as unfortunately I have more than 10 cells to check.

Hello

I would say there is a better way than this but anyway:

In cell B1 enter 1. In B2 enter this formula:

Please Login or Register  to view this content.

Then drag that formula down however many rows you want (10 in the example you gave; so drag it from B2 to B10). What this formula does is increment by 1 if the cell in A matches the one above it, otherwise it resets to 1.

You can find the biggest sequence by using the MAX function. E.g. here it would be:

Please Login or Register  to view this content.

Hopefully that does the trick.

Originally Posted by ReportMaker

Hello

I would say there is a better way than this but anyway:

In cell B1 enter 1. In B2 enter this formula:

Please Login or Register  to view this content.

Then drag that formula down however many rows you want (10 in the example you gave; so drag it from B2 to B10). What this formula does is increment by 1 if the cell in A matches the one above it, otherwise it resets to 1.

You can find the biggest sequence by using the MAX function. E.g. here it would be:

Please Login or Register  to view this content.

Hopefully that does the trick.

So simple! Thaks very much, does what I need fine.