First populated cell in row array/ Last populated cell in row arra

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Skyscan - 13 Jun 2007 11:57 GMT

Hello!

I have data in a single row array. The data is not contiguous such that
there are holes in the data. Several cells may be blank in the first few
cells, in the middle cells, or in the last cells. I need to identify which
cell is the one where the data begins. In addition, I need to identify which
cell is the one where the data ends. It is not necessary to locate "holes"
in the data set. I would like to use functions only to identify these first
and last cells.

Thank you very much!!

Tom

Reply to this Message

Bernie Deitrick - 13 Jun 2007 14:18 GMT

Skyscan,

Array enter each of these (enter using Ctrl-Shift-Enter)

First filled cell of row 2:
=ADDRESS(ROW(2:2),MIN(IF(2:2<>"",COLUMN(2:2),256)))

Last filled cell of row 2:
=ADDRESS(ROW(2:2),MAX(IF(2:2<>"",COLUMN(2:2),0)))

HTH,
Bernie
MS Excel MVP

> Hello!
>
[quoted text clipped - 9 lines]
>
> Tom

Reply to this Message

Skyscan - 15 Jun 2007 03:48 GMT

Thank you very much!!

Tom

> Skyscan,
>
[quoted text clipped - 23 lines]
> >
> > Tom

Reply to this Message

Skyscan - 15 Jun 2007 03:53 GMT

Sorry, but I was not able to get it to work. Please explain the comment
about array entering using Cntrl-Shift-Enter.

Thanks!

Tom

> Thank you very much!!
>
[quoted text clipped - 27 lines]
> > >
> > > Tom

Reply to this Message

Bernie Deitrick - 15 Jun 2007 04:00 GMT

Tom,

Instead of typing in the formula and pressing <Enter>, you need to type the
formula in, hold down the <Ctrl> key, hold down the <Shift> key, and while
those two keys are held down, press the <Enter> key. This enters the
formula as an array formula, which is a special formula type that evaluates
each cell in the range on a cell by cell basis, rather than all at once.

If you cannot get the formulas to work, I will send you a sample workbook
with the formulas working, if you post your email address or contact me
privately.

HTH,
Bernie
MS Excel MVP

> Sorry, but I was not able to get it to work. Please explain the comment
> about array entering using Cntrl-Shift-Enter.
[quoted text clipped - 40 lines]
>> > >
>> > > Tom

Reply to this Message

Skyscan - 15 Jun 2007 04:29 GMT

Thanks Bernie. I believe I did as you stated. I tried experimenting with
the position of the numbers in row 2. The result of the function shows
either $A$2 or $IV$2. When I go to Insert, Function and I look at the
template box ... I see the result is correct- it shows the location of the
cell where the data begins. Why would it only show correctly there, but not
as a result in the cell?

I'm sorry to ask another question here, but is there a function I can use to
find the contents of the first populated cell in that row?

Thanks!

Tom

> Tom,
>
[quoted text clipped - 56 lines]
> >> > >
> >> > > Tom

Reply to this Message

Bernie Deitrick - 15 Jun 2007 13:27 GMT

Tom,

If your row is entirely blank, the first function will return $IV$2, and the second one will return
a #VALUE! error.

But I don't know why you are getting the results that you show. Are the cells truly blank, or do
they have formulas in them?

Try selecting the entire row, pressing Delete, then typing something into one cell of that row.
Both formulas should return the same address.

Contact me a deitbe at consumer dot org and I will send you a working example, or email me a copy of
your workbook and I will take a look at it.

HTH,
Bernie
MS Excel MVP

> Thanks Bernie. I believe I did as you stated. I tried experimenting with
> the position of the numbers in row 2. The result of the function shows
[quoted text clipped - 70 lines]
>> >> > >
>> >> > > Tom

Reply to this Message

Support Request - 29 May 2008 17:20 GMT

Hi

I am trying on a worksheet where I need to do the following:
first, at the end of the worksheet entries, display the sum of each category and this should be automated:
example:
if column XYZ contains - a,a,b,c,d,a,b,c,d

expected result should be:
Total: a - 3
b - 2
c - 2
d - 2

Second, automatically calculate the entries in a column with number, which are not continuous
example:
if column 123 contains - 1,2, ,3, , , 4, 1

Expected result should be:
Total: 11

I need to automate the above two and for this, I need to identify the first and last rows in the sheet to use in the formula.

Any help is appreciated.

Reply to this Message