I'm using this array formula to find the next occupied row up, in
another column, from the current row:

{=(IF(D2<"",ROW(),ROW(INDIRECT("D"&MAX(IF($D$1:D2 ="",0,ROW($D
$1:D2)))))))}

It works, but the data is already over 13,000 rows, and the project is
trending toward around 30,000 rows. The formula is fast for the first
few thousand or so rows, but becomes very slow toward the last few
thousand or so rows, due to doing so many comparisons.

Is there a more efficient way to get the next occupied row up, in
another column, from the current row?

There probably won't ever be more than a thousand rows between
occupied cells (though there could be; there is no limit), so I could
change the formula at around row 1,000 to only look as far up as 1,000
rows above. That would calculate faster, but eventually this will be
used by others who will be inserting and deleting rows, and I'd prefer
something more robust.

Any ideas?


Thanks,

Greg

="D"&MATCH(LOOKUP(2,1/(D1:D65535<""),D1:D65535),D1:D65535)


"Greg Lovern" wrote:

I'm using this array formula to find the next occupied row up, in
another column, from the current row:

{=(IF(D2<"",ROW(),ROW(INDIRECT("D"&MAX(IF($D$1:D2 ="",0,ROW($D
$1:D2)))))))}

It works, but the data is already over 13,000 rows, and the project is
trending toward around 30,000 rows. The formula is fast for the first
few thousand or so rows, but becomes very slow toward the last few
thousand or so rows, due to doing so many comparisons.

Is there a more efficient way to get the next occupied row up, in
another column, from the current row?

There probably won't ever be more than a thousand rows between
occupied cells (though there could be; there is no limit), so I could
change the formula at around row 1,000 to only look as far up as 1,000
rows above. That would calculate faster, but eventually this will be
used by others who will be inserting and deleting rows, and I'd prefer
something more robust.

Any ideas?


Thanks,

Greg

On Nov 16, 1:54 pm, Teethless mama
wrote:
="D"&MATCH(LOOKUP(2,1/(D1:D65535<""),D1:D65535),D1:D65535)

Thanks, but I'm getting incorrect results with that starting at around
row 3,700. I'm using Excel 2007.

First, I changed it to return only the row number, and to look from
row 1 down to the current row:

=MATCH(LOOKUP(2,1/(D$1:D3742<""),D$1:D3742),D$1:D3742,0)

With D3689, D3691, D3693, and D3695 all occupied, it returns 3689 for
row 3691 and all rows below that, no matter what cells in column D
below that are occupied.

Excel bug??


Thanks,

Greg

I'm using this array formula to find the next occupied row up

Occupied with what exactly? A text value? A numeric value? A formula? A
formula that might return a blank ("") ? How about the entire column, is the
data one type (text or numeric) or is it mixed?

I don't think you need to use INDIRECT. Getting rid of INDIRECT will make a
significant improvement (at least, it should). Also, this can be done
without array entering so there's some additional efficiency gained.

I'm assuming there will never be a completely empty column. If there is your
current formula returns a #REF! error and the one I'm about to suggest will
return an #N/A error.

*Assuming* the data is numeric *and* the values will *always* be <1000:

=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can come up with a version that works for text or mixed values as well.

--
Biff
Microsoft Excel MVP


"Greg Lovern" wrote in message
...
I'm using this array formula to find the next occupied row up, in
another column, from the current row:

{=(IF(D2<"",ROW(),ROW(INDIRECT("D"&MAX(IF($D$1:D2 ="",0,ROW($D
$1:D2)))))))}

It works, but the data is already over 13,000 rows, and the project is
trending toward around 30,000 rows. The formula is fast for the first
few thousand or so rows, but becomes very slow toward the last few
thousand or so rows, due to doing so many comparisons.

Is there a more efficient way to get the next occupied row up, in
another column, from the current row?

There probably won't ever be more than a thousand rows between
occupied cells (though there could be; there is no limit), so I could
change the formula at around row 1,000 to only look as far up as 1,000
rows above. That would calculate faster, but eventually this will be
used by others who will be inserting and deleting rows, and I'd prefer
something more robust.

Any ideas?


Thanks,

Greg

*Assuming* the data is numeric *and* the values will *always* be <1000:
=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can even reduce that further to:

=IF(D2<"",ROW(),MATCH(1000,D$1:D1))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I'm using this array formula to find the next occupied row up

Occupied with what exactly? A text value? A numeric value? A formula? A
formula that might return a blank ("") ? How about the entire column, is
the data one type (text or numeric) or is it mixed?

I don't think you need to use INDIRECT. Getting rid of INDIRECT will make
a significant improvement (at least, it should). Also, this can be done
without array entering so there's some additional efficiency gained.

I'm assuming there will never be a completely empty column. If there is
your current formula returns a #REF! error and the one I'm about to
suggest will return an #N/A error.

*Assuming* the data is numeric *and* the values will *always* be <1000:

=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can come up with a version that works for text or mixed values as well.

--
Biff
Microsoft Excel MVP


"Greg Lovern" wrote in message
...
I'm using this array formula to find the next occupied row up, in
another column, from the current row:

{=(IF(D2<"",ROW(),ROW(INDIRECT("D"&MAX(IF($D$1:D2 ="",0,ROW($D
$1:D2)))))))}

It works, but the data is already over 13,000 rows, and the project is
trending toward around 30,000 rows. The formula is fast for the first
few thousand or so rows, but becomes very slow toward the last few
thousand or so rows, due to doing so many comparisons.

Is there a more efficient way to get the next occupied row up, in
another column, from the current row?

There probably won't ever be more than a thousand rows between
occupied cells (though there could be; there is no limit), so I could
change the formula at around row 1,000 to only look as far up as 1,000
rows above. That would calculate faster, but eventually this will be
used by others who will be inserting and deleting rows, and I'd prefer
something more robust.

Any ideas?


Thanks,

Greg

On Nov 16, 2:29 pm, "T. Valko" wrote:
*Assuming* the data is numeric *and* the values will *always* be <1000:
=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can even reduce that further to:

=IF(D2<"",ROW(),MATCH(1000,D$1:D1))

Thanks, I changed that to this to work with text:

=IF(D13375<"",ROW(),MATCH("ÿÿÿ",D$1:D13374))

And it works great. Pretty fast too.

The "ÿ" is ascii 255.

Greg

On Nov 16, 2:19 pm, "T. Valko" wrote:
I'm using this array formula to find the next occupied row up

Occupied with what exactly? A text value? A numeric value? A formula? A
formula that might return a blank ("") ? How about the entire column, is the
data one type (text or numeric) or is it mixed?

Occupied with a text value. Not a formula.

It may be that someone sometime in the future may have to enter a text
value that looks like a number, but the whole column is formatted as
text, so Excel should treat it as text.


Thanks,

Greg


I don't think you need to use INDIRECT. Getting rid of INDIRECT will make a
significant improvement (at least, it should). Also, this can be done
without array entering so there's some additional efficiency gained.

I'm assuming there will never be a completely empty column. If there is your
current formula returns a #REF! error and the one I'm about to suggest will
return an #N/A error.

*Assuming* the data is numeric *and* the values will *always* be <1000:

=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can come up with a version that works for text or mixed values as well.

--
Biff
Microsoft Excel MVP

Occupied with a text value.
the whole column is formatted as text

Ok, try this:


=IF(D2<"",ROW(),MATCH(REPT("z",255),D$1:D1))


--
Biff
Microsoft Excel MVP


"Greg Lovern" wrote in message
...
On Nov 16, 2:19 pm, "T. Valko" wrote:
I'm using this array formula to find the next occupied row up

Occupied with what exactly? A text value? A numeric value? A formula? A
formula that might return a blank ("") ? How about the entire column, is
the
data one type (text or numeric) or is it mixed?

Occupied with a text value. Not a formula.

It may be that someone sometime in the future may have to enter a text
value that looks like a number, but the whole column is formatted as
text, so Excel should treat it as text.


Thanks,

Greg


I don't think you need to use INDIRECT. Getting rid of INDIRECT will make
a
significant improvement (at least, it should). Also, this can be done
without array entering so there's some additional efficiency gained.

I'm assuming there will never be a completely empty column. If there is
your
current formula returns a #REF! error and the one I'm about to suggest
will
return an #N/A error.

*Assuming* the data is numeric *and* the values will *always* be <1000:

=IF(D2<"",ROW(),ROW(INDEX(D:D,MATCH(1000,D$1:D1)) ))

We can come up with a version that works for text or mixed values as
well.

--
Biff
Microsoft Excel MVP

On Nov 16, 3:01 pm, "T. Valko" wrote:
Occupied with a text value.
the whole column is formatted as text

Ok, try this:

=IF(D2<"",ROW(),MATCH(REPT("z",255),D$1:D1))

Thanks, but this is working great:

=IF(D13375<"",ROW(),MATCH("ÿÿÿ",D$1:D13374))


Greg

"Greg Lovern" wrote in message
...
On Nov 16, 3:01 pm, "T. Valko" wrote:
Occupied with a text value.
the whole column is formatted as text

Ok, try this:

=IF(D2<"",ROW(),MATCH(REPT("z",255),D$1:D1))
**********
Thanks, but this is working great:

=IF(D13375<"",ROW(),MATCH("ÿÿÿ",D$1:D13374))


Greg
**********

OK, if that's working for you, great!

However, I don't know what kind of text data you're working with but
MATCH("ÿÿÿ"....) fails on entries like:

zebra
xray
ultra

MATCH(REPT("z",255) works on *any* text entry. At least, I've never run
across a situation where it fails.

--
Biff
Microsoft Excel MVP