finding the maximum number of used cells in any column of a table

[Tim]

[sorry if this appears cross-posted... the original is in
microsoft.public.excel.worksheetfunctions but that seems like a 'dead'
group]

Hi All,

I have a table of data, but not all of the cells in each column will be
filled. i am
trying to find a worksheet function that will return the maximum
number of rows filled by any of the coumns in the range. the result
will go on another sheet in the same workbook (ie, this is where the
excel function will be).

I can do it by using: -

=max(counta(A:A), counta(B:B), counta(C:C)) etc etc,

but in one case i have 41 columns (!) so i was wondering if there is
an easier way... i thought i might be able to use an array formula but
so far that has been unsuccessful. i know i could do it with VBA but
i am trying to avoid this route at the moment.

to clarify: the final answer will be a single cell indicating the maximum
number of cells filled in any one column (i don't need to know which column)

Thanks for any guidance,

Tim

 

[Ron Coderre]

Try one of these:

For only a few columns (eg 8 columns):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,{0,1,2,3,4,5,6,7})))

Or...for 41 columns
(ARRAY FORMULA, committed with Ctrl+Shift+Enter, instead of just Enter):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)))

Or...for 41 columns (NON-array formula):
=MAX(INDEX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)),0))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

or this may work

=COUNTA(Sheet1!A)/4

In anycase you need to reference the sheet to which the name refers so Ron's
second formula

=MAX(INDEX(SUBTOTAL(3,OFFSET(Sheet1!$A:$A,,ROW(INDEX(Sheet1!A:A,1):INDEX(Sheet1!A:A,41))-1)),0))

 

[Tim]

brilliant - thank you (both)

out of interest, of the last two, is there any benefit in using an array
formula vs a non-array (in this instance)?

thanks

Tim

Try one of these:

For only a few columns (eg 8 columns):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,{0,1,2,3,4,5,6,7})))

Or...for 41 columns
(ARRAY FORMULA, committed with Ctrl+Shift+Enter, instead of just Enter):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)))

Or...for 41 columns (NON-array formula):
=MAX(INDEX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)),0))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

[sorry if this appears cross-posted... the original is in
microsoft.public.excel.worksheetfunctions but that seems like a 'dead'
group]

Hi All,

I have a table of data, but not all of the cells in each column will be
filled. i am
trying to find a worksheet function that will return the maximum
number of rows filled by any of the coumns in the range. the result
will go on another sheet in the same workbook (ie, this is where the
excel function will be).

I can do it by using: -

=max(counta(A:A), counta(B:B), counta(C:C)) etc etc,

but in one case i have 41 columns (!) so i was wondering if there is
an easier way... i thought i might be able to use an array formula but
so far that has been unsuccessful. i know i could do it with VBA but
i am trying to avoid this route at the moment.

to clarify: the final answer will be a single cell indicating the maximum
number of cells filled in any one column (i don't need to know which
column)

Thanks for any guidance,

Tim

 

[Ron Coderre]

Hi, Tim

I haven't clocked the array vs non-array versions. I generally suggest
non-array versions because they don't require Ctrl+Shift+Enter to make them
functional. Whenever *my* typical users edit an array formula, they ALWAYS
forget to C+S+E and there's no automatic flag in the cell to remind them.
Although, I sometimes tack this reminder on the end of array formulas with
no non-array alternative:

+N("commit with C+S+E")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

brilliant - thank you (both)

out of interest, of the last two, is there any benefit in using an array
formula vs a non-array (in this instance)?

thanks

Tim

Try one of these:

For only a few columns (eg 8 columns):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,{0,1,2,3,4,5,6,7})))

Or...for 41 columns
(ARRAY FORMULA, committed with Ctrl+Shift+Enter, instead of just Enter):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)))

Or...for 41 columns (NON-array formula):
=MAX(INDEX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)),0))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

[sorry if this appears cross-posted... the original is in
microsoft.public.excel.worksheetfunctions but that seems like a 'dead'
group]

Hi All,

I have a table of data, but not all of the cells in each column will be
filled. i am
trying to find a worksheet function that will return the maximum
number of rows filled by any of the coumns in the range. the result
will go on another sheet in the same workbook (ie, this is where the
excel function will be).

I can do it by using: -

=max(counta(A:A), counta(B:B), counta(C:C)) etc etc,

but in one case i have 41 columns (!) so i was wondering if there is
an easier way... i thought i might be able to use an array formula but
so far that has been unsuccessful. i know i could do it with VBA but
i am trying to avoid this route at the moment.

to clarify: the final answer will be a single cell indicating the
maximum number of cells filled in any one column (i don't need to know
which column)

Thanks for any guidance,

Tim

 

[Tim]

.... helps alot!!

i normally think of myself as pretty competent with Excel (particularly
compared with my colleagues!) and i've obviously used all of those functions
you provided individually, but it would have taken me A LONG TIME to
construct those formulae.

once again, thank you Ron,

Tim

Hi, Tim

I haven't clocked the array vs non-array versions. I generally suggest
non-array versions because they don't require Ctrl+Shift+Enter to make
them functional. Whenever *my* typical users edit an array formula, they
ALWAYS forget to C+S+E and there's no automatic flag in the cell to remind
them. Although, I sometimes tack this reminder on the end of array
formulas with no non-array alternative:

+N("commit with C+S+E")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

brilliant - thank you (both)

out of interest, of the last two, is there any benefit in using an array
formula vs a non-array (in this instance)?

thanks

Tim

Try one of these:

For only a few columns (eg 8 columns):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,{0,1,2,3,4,5,6,7})))

Or...for 41 columns
(ARRAY FORMULA, committed with Ctrl+Shift+Enter, instead of just Enter):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)))

Or...for 41 columns (NON-array formula):
=MAX(INDEX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)),0))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Tim" <tmarsh-trousers-@-take off my trousers to reply-blueyonder.co.uk>
wrote in message [sorry if this appears cross-posted... the original is in
microsoft.public.excel.worksheetfunctions but that seems like a 'dead'
group]

Hi All,

I have a table of data, but not all of the cells in each column will be
filled. i am
trying to find a worksheet function that will return the maximum
number of rows filled by any of the coumns in the range. the result
will go on another sheet in the same workbook (ie, this is where the
excel function will be).

I can do it by using: -

=max(counta(A:A), counta(B:B), counta(C:C)) etc etc,

but in one case i have 41 columns (!) so i was wondering if there is
an easier way... i thought i might be able to use an array formula but
so far that has been unsuccessful. i know i could do it with VBA but
i am trying to avoid this route at the moment.

to clarify: the final answer will be a single cell indicating the
maximum number of cells filled in any one column (i don't need to know
which column)

Thanks for any guidance,

Tim

 

[Ron Coderre]

Hi, Tim

I'm glad I could help!
-------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

... helps alot!!

i normally think of myself as pretty competent with Excel (particularly
compared with my colleagues!) and i've obviously used all of those
functions you provided individually, but it would have taken me A LONG
TIME to construct those formulae.

once again, thank you Ron,

Tim

Hi, Tim

I haven't clocked the array vs non-array versions. I generally suggest
non-array versions because they don't require Ctrl+Shift+Enter to make
them functional. Whenever *my* typical users edit an array formula, they
ALWAYS forget to C+S+E and there's no automatic flag in the cell to
remind them. Although, I sometimes tack this reminder on the end of array
formulas with no non-array alternative:

+N("commit with C+S+E")

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

brilliant - thank you (both)

out of interest, of the last two, is there any benefit in using an
array formula vs a non-array (in this instance)?

thanks

Tim

Try one of these:

For only a few columns (eg 8 columns):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,{0,1,2,3,4,5,6,7})))

Or...for 41 columns
(ARRAY FORMULA, committed with Ctrl+Shift+Enter, instead of just
Enter):
=MAX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)))

Or...for 41 columns (NON-array formula):
=MAX(INDEX(SUBTOTAL(3,OFFSET($A:$A,,ROW(INDEX(A:A,1):INDEX(A:A,41))-1)),0))


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"Tim" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk> wrote in message
[sorry if this appears cross-posted... the original is in
microsoft.public.excel.worksheetfunctions but that seems like a 'dead'
group]

Hi All,

I have a table of data, but not all of the cells in each column will
be filled. i am
trying to find a worksheet function that will return the maximum
number of rows filled by any of the coumns in the range. the result
will go on another sheet in the same workbook (ie, this is where the
excel function will be).

I can do it by using: -

=max(counta(A:A), counta(B:B), counta(C:C)) etc etc,

but in one case i have 41 columns (!) so i was wondering if there is
an easier way... i thought i might be able to use an array formula but
so far that has been unsuccessful. i know i could do it with VBA but
i am trying to avoid this route at the moment.

to clarify: the final answer will be a single cell indicating the
maximum number of cells filled in any one column (i don't need to know
which column)

Thanks for any guidance,

Tim