Find next occupied row up in another column

[Greg Lovern]

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$12="",0,ROW($D
$12)))))))}

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

 

[Guest]

="D"&MATCH(LOOKUP(2,1/(D165535<>""),D165535),D165535)

 

[T. Valko]

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,MATCH(1000,D$11))))

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

 

[T. Valko]

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

=IF(D2<>"",ROW(),ROW(INDEX(D,MATCH(1000,D$11))))

We can even reduce that further to:

=IF(D2<>"",ROW(),MATCH(1000,D$11))

 

[Greg Lovern]

="D"&MATCH(LOOKUP(2,1/(D165535<>""),D165535),D165535)

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$13742<>""),D$13742),D$13742,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

 

[Greg Lovern]

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

 

[Greg Lovern]

We can even reduce that further to:

=IF(D2<>"",ROW(),MATCH(1000,D$11))

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

=IF(D13375<>"",ROW(),MATCH("ÿÿÿ",D$113374))

And it works great. Pretty fast too.

The "ÿ" is ascii 255.

Greg

 

[T. Valko]

Occupied with a text value.

the whole column is formatted as text

Ok, try this:


=IF(D2<>"",ROW(),MATCH(REPT("z",255),D$11))

 

[Greg Lovern]

Ok, try this:

=IF(D2<>"",ROW(),MATCH(REPT("z",255),D$11))

Thanks, but this is working great:

=IF(D13375<>"",ROW(),MATCH("ÿÿÿ",D$113374))


Greg

 

[T. Valko]

Ok, try this:

=IF(D2<>"",ROW(),MATCH(REPT("z",255),D$11))

**********
Thanks, but this is working great:

=IF(D13375<>"",ROW(),MATCH("ÿÿÿ",D$113374))


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.