[Solved] Show the number of first non-empty row
[Solved] Show the number of first non-empty row
I need a formula that will show the number of first non-empty row in a column. In the attached image if I do this for column A, it will show 5, since it's the first row, that is not empty.
- Attachments
-
- column.png (2.14 KiB) Viewed 1291 times
Last edited by robleyd on Sun Jun 17, 2018 8:04 am, edited 2 times in total.
Reason: Tagged [Solved] [robleyd, Moderator]
Reason: Tagged [Solved] [robleyd, Moderator]
OpenOffice 4.1.1
Windows 7 64bit
Windows 7 64bit
Re: Show the number of first non-empty row
An array formula, which takes some cpu resources if used on the entire column:
To enter as an array formula, use ctrl+shift+enter. Successful array formula entry is shown by curly braces appearing around the entire formula.
If the range given is all empty, this formula returns an "impossible value": one more than the number of rows in the range.
If you are OK with regular expressions enabled for formulas, this formula is lighter on system resources:
Enable regex in Tools - Options - Calc - Calculate
If the range given is all empty, this formula returns the #N/A error.
Regular expressions enabled means that some lookup/compare functions/operators will use certain characters for special purposes, so it may make a mess of something that looks like a simple find/lookup/compare. I don't recommend that you use it for important work unless you understand it well.
Code: Select all
=MIN(ROW(A1:A1048576)+ROWS(A1:A1048576)*ISBLANK(A1:A1048576)
If the range given is all empty, this formula returns an "impossible value": one more than the number of rows in the range.
If you are OK with regular expressions enabled for formulas, this formula is lighter on system resources:
Code: Select all
=MATCH(".*";A1:A1048576;0)
If the range given is all empty, this formula returns the #N/A error.
Regular expressions enabled means that some lookup/compare functions/operators will use certain characters for special purposes, so it may make a mess of something that looks like a simple find/lookup/compare. I don't recommend that you use it for important work unless you understand it well.
Re: Show the number of first non-empty row
You may also use Ctrl+Up/Down in column A
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice