Excel Conundrum......

[ToraToraTora]

Ok I'm not a Jedi Master of Excel but I count myself as a reasonable padawan, anyway this is what I'd like to do: I have of course googled but nothing as yet does what I want. OK I have a column the cells of which are either populated or not. I would like to obtain a value for the longest section of contiguously populated cells in that column. eg so Col A reading down has. A,B,C,blank,blank,A,B, blank, A,B,C,D,blank,A - I would like to extract the number 4 from that, ie A,B,C,D is the longest contiguously populated sequence in the column. Any ideas? thanks.

[bhg481]

Test column A for blank, if it is not add 1 to value of new column, if it is zero new column.
New column will contain 1,2,3,0,0,1,2,0,1,2,3,4,0,1
search new column for largest number = longest number of non-zero cells.

[ABerrant]

Assuming Column A with 20 Rows

=MAX(FREQUENCY(IF(A1:A20"",ROW(A1:A20)),IF(A1:A20="",ROW(A1:A20))))

This is an array formula and must be entered with Control + Shift + Enter, except in Excel 365 which has native control of array formulas .

If entered correctly you'll see the formula wrapped in curly braces {} in the formula bar.

[New Judge]

There is an "=" missing after the first A20.

[bhg481]

A more elegant solution than mine from someone who, clearly, has a better knowledge of Excel than I do (I've never used it professionally).

[ABerrant]

It is indeed, which is strange because i copied the formula straight from my Excel formula bar after testing.

Also, in my browser the formula extends beyond the text box as one complete line; I wonder if these two phenomena are linked.

[ABerrant]

^ re the missing ' = '

[ABerrant]

Scratch that - it is not an EQUAL ' = ' missing but a NOT EQUAL ' ' in the first condition.

[ABerrant]

It is the NOT EQUAL which is breaking the text which I presume is being interpreted as formatting tag.

/\ flipped on its side
\/

[ABerrant]

...and just because this text formatting issue has been bugging me here is a re-jigged version of the formula where the EQUAL is NOTted to replicate NOT EQUAL

=MAX(FREQUENCY(IF(NOT(A1:A20=""),ROW(A1:A20)),IF(A1:A20="",ROW(A1:A20))))

[youngmafbog]

For more complex things just use VB, much easier and can be reused for other things in the future and also tweaked if you change the spec.

[ToraToraTora]

First of all bhg thanks that's a way of doing it that I had not considered and that would work. However, hats off to ABerrant that is genius, not done much in the way of array functions so I'm grateful to you for that, I got it working in a test sheet but not in my main sheet but that's probably me not typing it correctly. Anyway BA, well earned.

[ToraToraTora]

Ah penny dropped! the col contains numeric data thus I needed >0 and

[ToraToraTora]

Oddly enough YMB I have used VB a lot at work for different things so I was going to do that but I thought I'd put this one out there first!

[ToraToraTora]

17:27 should say
Ah penny dropped! the col contains numeric data thus I needed >0 and

[ToraToraTora]

dunno what's happening AB is chopping my post? ah just realised it doesn't like less than greater than signs.
12:27 should read:
Ah penny dropped! the col contains numeric data thus I needed greater than 0 and less than 1 as the 2 tests. Works a treat now!