After some tests I think that the most elegant one is to obtain the array using the "index" with the "Isblank" and the "zero column" trick, then convert the bounch of true and false in a bounch of one and zeros putting the "double negative" in front of it, then match the position of the first zero inside this array using "zero" for the argument of the "match", and that shuld give to the external "index" the position of the first "non empty" cell ! Hope my description is understandable :)))))
Very useful post, Mike. What if we want to show first the non-blankcell, and later the second non-blank value, and the third one...? Like deleting the empty lines...I think about a conditional function, where the array changes with the location of the previous found value. For example, if your first value is found in row 4, the next array will go from 5 to 10 and not from 1 to 10. I would appreciate some help. Thanks
actually I need to get the header of last filled cell whether txt or nos , same example you have mentioned I need to get day # for the last filled cell dynamically , so always get the day number whenever coping the formula down.
Cool! That is a great idea!! If you download the Excel workbook or look in the video I have placed such a formula in cell E8 and wrote "No CSE" next to it. Do you have a preference about which you like better: 1) FALSE and ISBLANK 2) TRUE and E2:L2 not ""
You are right! I didn't pay attention about the computational time. My bad :) I totally agree with you. Thx for the explanation and congrats for the great work!
Hello Mike, maybe it is possible to avoid the CTRL+SHIFT combination putting the "isblank" inside an "index" function with the row number set on 1 and column number set on zero, it should retourn an array inside the match without CTRL+SHIFT .
Nice one. But please what about if you want to find a last but one non zero cell in a range of a column? How can I go about it? last but one non-zero cell. please help me.
Becouse i think that this "MATCH(0;INDEX(--ISBLANK("range");1;0);0)" version of the formula it is very elegant :))). In one of my experiment I have subtracted the "Isblank" from 1, converting every "True" to "zero" and every "false" to "one", and in that case it is possible to use "1" for the argument of the match, but at the end i think the double negative its mouch more elegant :))).
This is really useful. Using this, we can find the first cell location containing data, but is there any way to find the last cell location containing data?
Hollo, Plz what if I want to find the second or third value. Cause I have a customer's which they have different repurchase so I need to figure out first sales /second /third ...)
Going one step further, how do you think this could be used to copy data from a master entry sheet into a dynamic sub table, matching a criteria, into the first available blank cell? Currently stuck on this problem myself and it's been a real pain trying to figure it out!!!
This is great, but what if you need to search in specific columns ? Only in Day 1, Day 4, Day 6 etc. ? A reply would be super appreciated. I only need it to search in specific columns, not the whole line
Why do you need to convert the TRUEs and FALSEs to 1s and 0s? Can't you just skip that and use a lookup value of TRUE or FALSE? Like: =INDEX(range,MATCH(FALSE,INDEX(ISBLANK(range),),0))
Instead of 9.99E+307 I would have used MAX(E2:L2)+1. So you don't need to know the biggest number in Excel and still be sure to use a big enough number. This should work also, right?
There HAS to be a way to do this... for monthly reports, what is the code to make excel stop counting at the first blank cell? Ex., column C for August has 90 entries, how do I make Excel automatically stop at 91? I have to do this manually for like each month and a ton of columns
Ctrl+Shift+Enter that's what was missing, I had the logic many times but never worked. Thanks man. Is there anyway to return values other than the first non-blank?
Yes, the current class really rocks and it is soooo much fun to hang out "live" in a room full of Excelers.
Great job Mike. Smart class you have. Kudos to Joyce and April.
Thx man you saved my day,.... Actually you saved my entire investment analysis project
After some tests I think that the most elegant one is to obtain the array using the "index" with the "Isblank" and the "zero column" trick, then convert the bounch of true and false in a bounch of one and zeros putting the "double negative" in front of it, then match the position of the first zero inside this array using "zero" for the argument of the "match", and that shuld give to the external "index" the position of the first "non empty" cell ! Hope my description is understandable :)))))
Yes, that will work! If you had a large spreadsheet and calculation time was a factor, using a hard coded number would help to calculate faster.
You saved my day!!
Very useful post, Mike. What if we want to show first the non-blankcell, and later the second non-blank value, and the third one...? Like deleting the empty lines...I think about a conditional function, where the array changes with the location of the previous found value. For example, if your first value is found in row 4, the next array will go from 5 to 10 and not from 1 to 10. I would appreciate some help. Thanks
Thank you for the carry
You are welcome!
MAX is not bad; in fact it is a common cool trick!
That's excellent tips. I have a scenario where I need to look up the header info (in your example above = Day 1 or Day 3). How can I do that?
actually I need to get the header of last filled cell whether txt or nos , same example you have mentioned I need to get day # for the last filled cell dynamically , so always get the day number whenever coping the formula down.
Cool! That is a great idea!!
If you download the Excel workbook or look in the video I have placed such a formula in cell E8 and wrote "No CSE" next to it.
Do you have a preference about which you like better:
1) FALSE and ISBLANK
2) TRUE and E2:L2 not ""
You are right! I didn't pay attention about the computational time. My bad :) I totally agree with you.
Thx for the explanation and congrats for the great work!
Hi Mike,
How can be formula further be extended to pick header of first filled cell??
Very helpful.
Although syntax is fine, for me the ISBLANK is more explicit.
Hi. How can I extract data for the next non-zero and non-blank cell after the first value? I hope I can be helped on this one. Thank you!
Hello Mike, maybe it is possible to avoid the CTRL+SHIFT combination putting the "isblank" inside an "index" function with the row number set on 1 and column number set on zero, it should retourn an array inside the match without CTRL+SHIFT .
Nice one. But please what about if you want to find a last but one non zero cell in a range of a column? How can I go about it? last but one non-zero cell. please help me.
Fantastic Mike, fantastic, this trick is amazing, you always say more in your videos
Glad it is fantastic! Thanks for your amazing support : )
Is it possible to get the arrange to look from right to left? Or more specifically for my need to look from down to up if using in a column?
Becouse i think that this "MATCH(0;INDEX(--ISBLANK("range");1;0);0)" version of the formula it is very elegant :))). In one of my experiment I have subtracted the "Isblank" from 1, converting every "True" to "zero" and every "false" to "one", and in that case it is possible to use "1" for the argument of the match, but at the end i think the double negative its mouch more elegant :))).
This is really useful. Using this, we can find the first cell location containing data, but is there any way to find the last cell location containing data?
I am not sure that I follow. Why do you have to convert to 1s and 0s?
Hi, Mike, what if I have text instead of numbers and would need to get the first cell which contains text?
Mike,
Is it possible to submit questions for solutions or projects as a video?
Thanks.
Hollo,
Plz what if I want to find the second or third value. Cause I have a customer's which they have different repurchase so I need to figure out first sales /second /third ...)
Going one step further, how do you think this could be used to copy data from a master entry sheet into a dynamic sub table, matching a criteria, into the first available blank cell? Currently stuck on this problem myself and it's been a real pain trying to figure it out!!!
This is great, but what if you need to search in specific columns ? Only in Day 1, Day 4, Day 6 etc. ? A reply would be super appreciated. I only need it to search in specific columns, not the whole line
what if the data range have subtotal, how to exclude so that I can get the last number
Sir, in row no 5, if i want second no i.e 12.5 than how can i do?
Why do you need to convert the TRUEs and FALSEs to 1s and 0s? Can't you just skip that and use a lookup value of TRUE or FALSE? Like:
=INDEX(range,MATCH(FALSE,INDEX(ISBLANK(range),),0))
Fun is good!!
Is it possible to lookup an alpha numeric value
Sure, post a video response.
Instead of 9.99E+307 I would have used MAX(E2:L2)+1. So you don't need to know the biggest number in Excel and still be sure to use a big enough number. This should work also, right?
There HAS to be a way to do this... for monthly reports, what is the code to make excel stop counting at the first blank cell?
Ex., column C for August has 90 entries, how do I make Excel automatically stop at 91? I have to do this manually for like each month and a ton of columns
How can I go back to the last cell position using an already established shortcut or by making a new one in Mac Excel 2011
Thank you so much Sir.
What if the last 2 or 3 values are duplicates?
how to do the same for text entries?
How to fetch the last non 0 value?
awesome thanks
Ctrl+Shift+Enter that's what was missing, I had the logic many times but never worked. Thanks man.
Is there anyway to return values other than the first non-blank?
I am sure there is. What is the logic of what the other is?
ExcelIsFun second third or other. But I got it already using small function . Thanks so much
@@hzaphry Cool : )
Good Job :) Thank You!
Awesome! Thank you!!
thanx alot
Great, thanks a lot!
Why we will use 9.99E+307 in lookup value
Thanks
Good video, but I Want Date 1, Date 2 etc. Instead of Numbers..Please reply..
Becouse it is fun :))))
Please help sir.