Find first blank cell in single-column range

Hi guys,

I haven't been able to do this in Excel 97 or Excel 2000... Is it possible to use MATCH or another function to find the position of the first blank cell in a single-column range?

I want the formula to return 7 if A7 is the first empty cell in column A.

It is important that this works correctly, even if there are used cells below the first empty cell.

(I want a normal worksheet formula. NOT a VBA solution. NOT an array formula.)

This doesn't work (it returns #N/A!):
  =MATCH("",A:A,0)

Ture Magnusson
Karlstad, Sweden
LVL 22
tureAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
calacucciaConnect With a Mentor Commented:
Well Ture,

this is a come-out of Argmysters question, isn't it. This is what I've been looking for for 2 days after you having posted the first answer on his question. My answer to your question, from this experience is simply: NO (and I'me sure you basicly came to the same conclusion during thee last days).

Surprisingly enough.

Cheers, Calaccucia.

PS This is my answer, but I don't post as an answer, want to see others opinion.
0
 
tureAuthor Commented:
Edited text of question.
0
 
tureAuthor Commented:
Calacuccia,

You are right, of course; I have been struggling with this for a few days, trying to find a non-array solution to Argmyster's Christmas question, but without success.

I find it strange that it's so hard to do this kínd of thing with MATCH.

Thanks for leaving the question unlocked.

/Ture
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
antratCommented:
How about using a dynamic named range of column A called MyRange using:
=OFFSET($A:$A,0,0,COUNTA($A:$A:),1)

and then in the cell you want the result:
=MATCH(0,MyRange,-1)+1

antrat

0
 
antratCommented:
How about a dynamic named range of column A called MyRange using:
=OFFSET($A:$A,0,0,COUNTA($A:$A:),1)

and then in the cell you want the result:
=MATCH(0,MyRange,-1)+1

antrat

0
 
tureAuthor Commented:
Antrat,

Thanks for your suggestion, but unfortunately it will not work if several areas in column A are used.

If values are entered in A1:A6 and in A50:A56 and A70:A71, the functon should return 7, which is the position of the first empty cell in column A.

Actually, Excel wouldn't even accept a named range referring to the formula you suggested.

/Ture
0
 
antratCommented:
DARN!
0
 
tureAuthor Commented:
Just hive it another try, Antrat...

:o)
0
 
tomk120999Commented:
Morning, antrat, you might try this scheme.  Admittedly it's a work around, but it gets you there.  The idea is to flag blank or non-blank cell in your range:

Say the range you want to test is in column A (A1:A27), in cell B1 put the formula  =ISBLANK(A1)  and copy it down the length of the range.  Name that range (B1:B27) as "Test1"

In C1 put the formula:
 =MATCH(TRUE,Test1,0)

I constructed range "Test" in col A (A1:A27) with A1:A6, A13:A18, and A24:A27 having entries.  My answer in cell C1 was 7.

Hope you can use this.
good luck as usual...
and Happy Holidays.
0
 
tureAuthor Commented:
tomk,

Thanks for your comment. Your solution works, but as you write - it is a work around and not what I am looking for.

What I really want is a non-array, non-VBA formula that works by itself, without extra 'assisting' cells. I can accept assistance by named ranges, but I'm not willing to use extra cells for this calculation.

I hate to be so picky, but I have put a lot of thought into this problem myself during the last few days and I'm looking for the good, solid solution that I couldn't come to think of.

/Ture
0
 
tomk120999Commented:
Are there any other named ranges that you might get assistance from?

Since it's obvious that the lookup functions won't accept a blank value for their lookup value, and you don't want to use additional cells to flag the entries, it sounds like you must come up with another scheme for identifying the blank cell.  One way might be to put a placeholder (like *space* or 0) in the cell instead of just having it blank (don't know if that's an option).  Then the lookup functions could pick it up.

If you consider the additional cells I mentioned "a named range", could you "hide" it by putting the Test1 range on another (hidden?) worksheet out of the way?  Interesting question.

good luck as usual...
0
 
tureAuthor Commented:
Tomk,

Thanks again for your comments.

Actually, finding the first blank cell in column A is not a problem. I know of several ways to achieve that. Cheating by searching for space or zero is not an option.

The challenge is to do it with only a worksheet formula. It bugs me that I can't find a way to do this with the MATCH function. I guess it just can't be done that easily.

/Ture
0
 
argmysterCommented:
Ture,

Without getting to involved here, I would say that no built-in Functions or formula using various Functions in Excel can find the First blank cell in a range and provide the desired effect you are looking for.

I can sum up the row numbers that are blank by using this little jewel:

=SUM(IF(ISBLANK(A1:A10),ROW(A1:A10)))

Ctrl + Shift + Enter (of course)

What ever rows are blank, the row numbers will be added up. This don't help because the sum of the rows is really of no value in your case.  

The problem with trying to use built-in functions to do what you ask, is that most functions don't stop at a first occurance of something, except for the LOOKUP, SEARCH, MATCH, FIND etc...and you have already determined that "" won't work with LOOKUP's & MATCH's.

So with this in mind, you may have to except this limitation of Excel's built-in functions.  I would say the only way to accomplish this would be a User Defined Function written in VBA.

Argmsyter...

Again, you never know, someone out there may find some obscure way to get at it, but I wouldn't think it could be possible without VBA.
0
 
tureAuthor Commented:
Argmyster,

Glad to see you here!

Your formula works quite well to find the first blank cell in column A, if we only change SUM to MIN (and add some size to it).

=MIN(IF(ISBLANK(A1:A65535),ROW(A1:A65535)))

Actually, I used this technique in a recently posted question. But it IS an array formula...

/Ture
0
 
antratCommented:
An array sure would make life easier!

Anyway I'm having fun trying. I have not solved the problem yet but in trying I have found a much cleaner and simpler way to make a Dynamic Range that will include an infinite amount of blank cells between data.
Place this in the insert name refers to box:

=OFFSET($A$2,0,0,MATCH(1000000000,$A:$A)-1,1)

the number 1000000000 is just a ridiculously high number that won't actually match any numbers in the range.

anyway back to the drawing board

antrat
0
 
antratCommented:
So near yet so far.

Here's a neat little array formula that will return the row number of the first blank cell in a range. First make a dynamic named range as described above called MyRange the use this:

=MATCH("0",MyRange & "",-1)+1

0
 
antratCommented:
actually here's a slightly better way, but alas still an array.

=MATCH("",MyRange & "",-1)

antrat

0
 
tureAuthor Commented:
Nice one, Antrat!

(Still not an answer to my question, but it's getting closer...)

You could actually avoid guessing the size of the 'ridiculously high number' by replacing it with MAX($A:$A)

=OFFSET($A$2,0,0,MATCH(MAX($A:$A),$A:$A)-1,1)

(At first, I thought that I had to search for MAX($A:$A)+1, but that's not necessary.)

Good things to know when struggling with problems like this - My tips to check the resulting MyRange:

Tip 1: =ROWS(MYRANGE)
Tip 2: Ctrl+G (Goto), Type 'myrange', Enter

/Ture
0
 
calacucciaCommented:
Antrat, Ture,

The MATCH("";MyRange & "";-1) function just gives me a #VALUE result, with MyRange defined as you did.

What's happening here. Would the ";" notation instead of "," do any harm or cause any difference??????

Calacuccia
0
 
antratCommented:
Using ; instead of , only depends on the setting on your PC

Did you enter it as an array? ie Ctrl+Shift+Enter

antrat
0
 
argmysterCommented:
Ture,

It was late and I totally missed the MIN vs. SUM in my comment.  

I don't believe you will find a solution without using the Array Formula Foundation in Excel.

But, we will see...if it can be figured out, here is the place it will be done.

Argmyster...
0
 
calacucciaCommented:
Right Antrat,

thanks a lot, I rarely used array functions (and never to return a single result).

Anyway, still don't believe there's any way to make Excel find the blank cell by using the standard functions. Tried some things, but nothing gets past the #N/A result.

Calacuccia
0
 
antratCommented:
Never say never when dealing with Excel!

Ok here is how it can be done:

1:Create a Dynamic Named Range called "MyRange" refined even futher than before but will still not go past the last entry, but will include all blank rows:

=OFFSET($A$2,0,0,MATCH(,$A:$A,-1)-1,1)


2:Create another Dynamic Named Range called "AntratsRange" like below:

=OFFSET($A$2,0,0,MATCH("",MyRange & "",-1),1)

3:Enter =Rows("AntratsRange") in any cell and you have the answer.

antrat

0
 
antratCommented:
Oops # 3 should read:

3:Enter =Rows(AntratsRange)in any cell and you have the answer.



0
 
argmysterCommented:
Antrat

Good Work!

The Dynamic Named Range is very powerful once you understand it!

Merry Xmas

Argmyster...
0
 
argmysterCommented:
Antrat and Ture
Actually, after looking at the results, I believe you might want to change the first Dynamic Named Range to:
=OFFSET($A$2,0,0,MATCH("",MyRange & "",-1),1)

for the true row number to be displayed by the formula =Rows(AntratsRange)

Argmyster...
0
 
argmysterCommented:
I mean =OFFSET($A$1,0,0,MATCH("",MyRange & "",-1),1)

Dang, I forgot to change your formula, sorry.

Argmyster...

0
 
antratCommented:
I have found a flaw in my answer, but have found a solution to it. My answer works fine until there is a Zero in the range then "AntratsRange" will only extend as far as the first zero. To overcome this change "MyRange" back to:

=OFFSET($A$2,0,0,MATCH(MAX($A:$A),$A:$A)-1,1)

instead of:

=OFFSET($A$2,0,0,MATCH(,$A:$A,-1)-1,1)

and to get the right row number as Argmyster has said, I think you would be better off using:

=Rows(AntratsRange)+1

That way neither Dynamic Named Ranges will include the heading.




antrat







0
 
tureAuthor Commented:
Calacuccia,
You were the first to give me a correct answer to my question. You wrote 'No'. It's not what I hoped for, but unfortunately it seems to be the correct answer. Therefore you will receive 200 points.

antrat,
Your answer does not satisfy all requirements, because your named range actually refers to an array formula. But you have given valuable input, so you'll receive 200 points in a separately posted question.

Argmyster,
Your Merry Christmas question is the reason why this question was posted at all. Because og you, we have all been able to share interesting ideas and come up with interesting solutions. Thanks! 200 points for you in a separate question!

tomk,
Your suggestion wasn't really what I was looking for. But I see you are relatively new at Experts Exchange so I'd like to give you a 'welcome gift'. And I like your info page. And what-the-heck-it's-Christmas... There are 200 points for you in a separately posted question.

Merry Christmas, All!

/Ture
0
 
antratCommented:
Ture, You are probaly right,but for a formula to be considered and array doesn't it have to be entered by Ctrl+Shift+Enter and have curly brackets.

Merrry Xmas
0
 
tureAuthor Commented:
Antrat,

When using a formula to define a name, you can (in some cases) use an array formula as part of the definition. Here is an example:

In this formula, used to define MyRange...
=OFFSET($A$2,0,0,MATCH(TRUE,ISBLANK($A$2:$A$65536),0)-1,1)

....this part is actually an array formula
MATCH(TRUE,ISBLANK($A$2:$A$65536),0)

MY APOLOGIES...
After examining your post again, I see that your solution was not an array formula. It was based on the use of two separate range names and I wrote in one of my comments to tomk that 'I can accept assistance by named ranges, but I'm not willing to use extra cells for this calculation.'

CONGRATULATIONS!
You have answered my question and solved the problem, meeting all of my requirements!

(But the points for this question have already been awarded to Calacuccia, who gave a correct answer based on the information available at the time of his posting.)

/Ture
0
 
antratCommented:
No problem

antrat
0
 
argmysterCommented:
Well, that's great!  Another solved problem. I didn't it think would be.  This has happened so many times, I think I'm getting rid of the words 'can't' & 'won't' & 'I don't think' in my vocabulary. 8~)  Espcially when dealing with Excel.

Thanks for the gift!

Argmyster...its good to see a plan come together.

0
 
calacucciaCommented:
Hi Ture,

Thanks for the X-mas gift.

Antrat, and everyone,

Have to add to this discussion that Antrat's solution is still an Array solution.

In the AntratsRange formula, Antrat uses the Match function written as:

MATCH("",MyRange & "",-1)

Well, this function on itself is an Array function, so even when the final formula is just =Rows(AntratRange) which is not an array function, he still uses an array function in the named range definition.

In my opinion, this is not the easy way to find the first empty cell Ture was looking for.

It still is a great workaround AntraT.

God Jul,

Calacuccia
0
 
antratCommented:
Your right, it certainly isn't the easiest way, 1 array on the sheet would be the easiest.

What confuses me is that the formula doesn't seem to fit the requirements to be an "array" and only refers to a single range.

From help:
Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas the same way that you create basic, single-value formulas. Select the cell or cells that will contain the formula, create the formula, and then press CTRL+SHIFT+ENTER to enter the formula.

antrat

0
All Courses

From novice to tech pro — start learning today.