Google Sheets - Get Last Non-Empty Cell in Row or Column
Вставка
- Опубліковано 7 вер 2024
- Learn how to get the last non-empty cell in row or column. In this tutorial we'll first create a formula to get the row number for last non-empty cell and then get the data value in the the cell.
#googlesheets #lastrow #lastcolumn
UA-cam is amazing. I just picked my car apart and put it back together. Now Im learning excel whoop whoop!
Hello. I was watching your videos for the last 3 days and i can tell you the way you explain is perfect. I am SAP consultant with ABAP development skills but i wanted to do something for me with google sheets and java script in which i never worked before ( only excel formulas ). Based on your videos i was able to write my own little program to track some consumptions in the house, etc...by passing values to google sheets and getting values from google sheets in java script. Great teacher !
Was poking around on the internet looking for a solution to this. Most of them were convoluted for no reason. This was straight to the point! Thanks for the great tutorial!
OMG, your channel basically covers all the functions needed for all situation !!
As always, you make it easy with no boring explanations, thank you, I'm glad I've found your channel !
Nice idea. It works too for a range : =max(arrayformula(ROW(A1:H)*(--(A1:H"")))) Very cool when used with indirect function
Great way of explaining! You are a born teacher! Thanks form the bottom
of my heart!
Thank you so much! I've been struggling so hard to do this for a long time.
What a sophisticated solution to multiply by 0 if the column is empty.
Me: "Why can't I figure this out, how hard can it be? It's probably a simple function like _SUM()_, that I just don't know about."
Tutorial: 16 minutes long
Me: "oh boy..."
Your way of teaching is really cool and intuitive. As always, really good video!
Yes! Not just "paste this code" but building the formulas. Then I can tell how my code will be similar/different.
best explanation ever....
I was always afraid of spreadsheet formulas but you made it super easy and understandable for me!
Thank you so much!
This one has a problems if the formula is in the actual row analysed. =(INDEX(FILTER(H44:44;NOT(ISBLANK(H44:44)));COUNTA(H44:44)) This one solves the problem.
god bless you! thank you very much!
Everything works right up until 12:08 with the new INDEX function. The cell simply remains empty!
Same
That is extremely useful. I have combined it with the indirect function to use with dynamic dropdown lists which is something I have been trying to do for a few weeks using information from another of your videos, but couldn’t get it to work.
Thank you for taking you time to make a video about this. This is really helpful. God bless!
Exactly what I needed, Thank you for this educational video!
thanks god I found this helpfull tip about COUNTA, which I use now in an empty cell on top to get the number of rows in my App-script.
I had a big problem with an ArrayFormula within the Data-range and had a ton of non-blank rows in my search-result.
👍
Great video. Thanks. Just to add that if you don't start form Column 1 or Array 1, you need to manually offset the value for MAX
Thanks for this amazing video. Very easy to understand and great shortcuts shared as well
Are you sure you're not David Copperfield! Great stuff, these videos are priceless, Thanks
that was some genius move, really. thanks for that!!
superb video....i am learning from your videos.... very help for archive my automation tasks....from Sri Lanka
Hi! Im a data analyst here from the philippines, Im working with my data table which leads me to here (your video), Although, what im really looking for is: how can I add a text after the last value on my data (row) like for example: if the last value is at row 10, row 11 automatically shows "Grand total" or any text that would fit the data table. Thanks a lot for your help!
Your videos are amazing. I have learned a lot. Thank you !
Great video, thank you for sharing this method!
If possible, I will give you a millions like bro. You solve my big problem with a small trick. 😍😘
😀
That's elegant! Thank you for sharing!
Dude... You are one Smart Guy! How would you make Flash Cards that Auto Expand in Google Sheets?
How can I get this to work in an array formula? To just fill out the entire column?
Hi sir...
New type of thing I have learned.
Please make more videos on merge google sheets with google docs without add-on. For certification or pre-defined letter pads like structure.
Thanks and regards
Waiting for your response.
Google docs Mail Merge w/o add-on available from his previous video:
ua-cam.com/video/QNPPEB64QbI/v-deo.html
Maybe we both want w/o add-on and no scripts too.
Waiting for that too.
Meanwhile, I guess pre-printed forms and a tab with query to display all necessary data should do the job.
what if i just want to looking for value in range of row. For example i just want to look last value in range A11:Z11. Thanks before
please let us know sum function in google query function, i mean to say that total showing in bottom like if ram sales repeat in 5times than not required group total of ram, we required total showing on both with each entry of ram ,
That's fantastic! Very useful technique.
Hi, thank you for this great video. My question is: Now that I have known the last row, how would I be able to concatenate or join them to make a simple range? For instance I have A1:A & maxrow. How can attach that number to the A1:A to make a range with the lastrow? I hope I am making sense. Please help.
Showing REF# error..please help. And error says circular ref. Check spreadsheet settings
We have same problem.
This formula can also be used =ArrayFormula(MAX(IF(A:A="",,ROW(A1:A))))
Great video, you explain everything well and in detail. However, in my case the result comes as an empty cell.
I figured it is because my array starts from the third row in a column, and my result should be in the second row of the same column.
Any way to fix this?
Very good videos Sir great 👍👍
How can add a filter to this? like give me the last row for a specific fruit. Where abouts would i add the filter?
beautifully explained as always - many thanks
Thanks a lot, exactly what i was looking for !
This is such a great video. Great method of explaining!
I need to use the row number he finds, in a cell. e.g. he find 8 as the row number. How i can use in another function C8 ?
Fascinating way to tackle the problem. I've played with this formula a bit and it is quite handy. I am, however, curious if it is possible to adjust this formula to do the same, but instead return the second last item in the list?
Use LARGE instead of MAX
You are AWESOME. Thank you!
A small help with a formula
I normally use index and match function with sumproduct and it just work fine in excel. But don’t know why same is not working in google sheets
I have two sheets . on sheet1 I have invoice numbers and on sheet2 I have cheque nos . I manually put invoice no against the cheque nos on sheet2.
I added 3 columns on sheet1 : Col 1 to sum the amount of the cheques in hand. Col 2 to Count the no of cheques against each invoice. I easily displayed the correct values in Col 1 and Col2 using Sumproduct function.
For Col3 as there could be many cheques against a single invoice. In col 3 I want to display the Cheque date for the very last Cheq for this Invoice.
e.g Invoice no is 2565 and Cheque dates against this invoice are { 10-Jun-20, 15-Jun-20, 25-Jun-20}
in Col3 I want to display 25-Jun-20
I easily located the row number on sheet2 where cheque date “25-Jun-20” is by using formula
=sumproduct(MAX(('CDC/PDC'!L:L=$B4)*ROW('CDC/PDC'!L:L)))
Say the output of this formula is 330
In excel if I use index function Index(rangA:A,[Row : I put 330], [col : I put 1])
I can easily display the value i.e 25-Jun-20 in row 330 of range Sheet2!A:A
But when I use below in google sheet it display value from all the rows from sheet2
=index('CDC/PDC'!C3:C,sumproduct(MAX(('CDC/PDC'!L3:L=$B4)*ROW('CDC/PDC'!L3:L))),1)
Since you changed your ranges to start from row 3, your ROW function will not match the position.
This however should work.
=index('CDC/PDC'!C:C,sumproduct(MAX(('CDC/PDC'!L:L=$B4)*ROW('CDC/PDC'!L:L))),1)
@@ExcelGoogleSheets brilliant you are such a blessing :)
Does this work if the rows you're trying to array index are outputs of another formula?
Oh my. Thank you so much. This helps a lot 🙂
This works great BUT doesn't work when the blank cells have formulas inside. I have NOT been able to find anything that works in a blank range filled with formulas.
thanks
love it
thanks. Sir.
I find it long time
Another awesome video! thanks. you should open a patreon too. I wouldn't mind chipping in some beer money
But please keep making. I'm loving learning from your videos
i really like your video because you explain it broken down to dum bits. However I have a question, I don't want to disable the re-iterative calculation but if I inquire about the last filled row on column A in a cell located in column A, what to do with the circular dependency?
Thank you so much ! This is very well explained ! :D
Superb!! highly appreciated
Hi I love your Videos have learnt so much from them and been able to do so much but i do have 1 question. Is there a way to use this formula to get the last known number from spasific date? I work with a group from germany and we get funds in euros but convert them to IDR when they arrive. The currency exchange rate changes a lot and quite drasticly over the years. I want to be able to get the euro prices for all expensise we have made. To do so correctly i wanna use the date of the expense to find the last time we converted euro to IDR to get the exchange rate. Hope u can help me
PLEASE, MY SHEET DID NOT RETURN THAT RESULT "GRAPES" @ THE TIME STAMP: 12:29 ; WHAT COULD BE THE REASON? I ENSURED THE FORMAT WAS ON "AUTOMATIC"
You have amazing explaining skills, thank you so so very much for your video!!!!!
:)
You’re the best!! Thank you for sharing this!
Excellent, thank you!
The tutorial was very clear and well done, but can someone explain what this formula is typically used for in a real world situation?
I have a trading journal where I populate each new cell with a win or a loss in EUR. I have another cell "Current Balance" which only needs to check the last populated cell and take the last value - the new balance on my account.
And another one : The last row containing data for a sheet named "test" : =max(arrayformula(ROW(indirect("test!1:" & ROWS(test!A:A)))*(--(indirect("test!1:" & ROWS(test!A:A))""))))
Good explication, thank you so much
Thank you very much.
Hello good sir,
I have a question - suppose you want to make a list of all the fruits after banana and the list is not uniform i.e. sometimes banana might be every other row, sometimes there are blank rows, sometimes the word banana might come up 3 rows later etc. Is there a reliable way to solve this problem?
Many thanks
Your videos are amazing!
Thank You!
This is a fantastic job. Love your videos
Good one. I need to request to combine multi column into one with same row values... Thanks
Hi. Thanks for this video. I am looking in getting the last 5 rows (multiple columns in these 5 rows). Any way?
It is really nice video, I am searching solutions little bit same like this video but some more complex, if you have solution please give, I want the column data in next sheets without empty cell but in primary sheets has the empty cells.
I have around 45 column & I am searching solutions to bring all 45 column data in next sheets in one column without empty cell
Bhawani Shekhawat use query function, query will be where d is not null , assuming d is the column which can never be blank if u have Data In its corresponding row
How could I do an importrange() referencing a different sheet and pull the last non-empty cell from a column? For example, I've tried =Importrange("my.google.sheet.address", index(b2:b, counta(b2:b))), but the index and counta only seem to look at the sheet the formula is entered on, NOT the referenced sheet.
I've also tried =index(importrange("my.google.sheet.address", counta(b2:b)) without success also.
=counta(importrange("my.google.sheet.address","b2:b"))
@@ExcelGoogleSheets thanks for the quick reply...i actually kinda figured it out from a different video you published (ua-cam.com/video/4XMB03BqNm4/v-deo.html) & just swapped in counta for match. 👍🏼
Excellent explained master, but I want know if is possible with scripts web app google create an user and password depending of spreadsheet
What if cell (A) contains value comes from a formula? And entire column has formula which produce nil value (means "empty") Then?
I tried but not working in above case.
So I need to do some kind of index and match function that incorporates this. I want to see the last status of en employee. So i need this to match with a certain employee based on their last status. What you did in this video is close to what I need. But I also need to figure out how to tell the last status of each worker all in the same column. any advice on this?
You are the GOAT
You da real MVP! Thank you.
thank you so much. You just save my day!
Thank you so much...
So amazing!! Thank you so much!
Glad you like it!
Thanks so much for this. However, the row function points to an absolute row position starting from Row 1. My array doesn't start from the first row. Is there a way to reference a relative value such that Row 1 is seen as the row where my data starts rather than the actual first row on the sheet? #NoobAlert
Isn't the last row number still the same? Just select the whole column.
@@ExcelGoogleSheets But Index function is not considering absolute position. For it first entry in the column is the first position.
Thanks. Very well done.
is there a way to 2nd to last value, 3rd to last, etc? Love your videos
add -1 -2 -3 after MATCH function
Dude this was awesome work, thanks
I love you! and your videos! thank you so much!!!
I need this formula to arrayformula down the column. It works in the initial cell, but doesn't populate down the column. Can that be done?
How can I highlight a last non blank cell in a column using conditional formatting.. ? Please help
Thank you. Very helpfull for me
thanks for the learning's. if Instead of row no.., I want to get the value in the last non empty cell, what changes do I have to make.
He showed it in the video
what about if used in data filtered columns to get last value ?
This video was very helpful in what I have been trying to do but I am needing to get the data form a different column. I have a sheet that the array formula would be for column F but I need to get the data from column E based on the array. How is the best way to do that? I also need to have the same data displayed on a separate sheet within the same workbook. I have found your videos very helpful. Trying to learn google. Thank you
When you use INDEX function just highlight column E
ua-cam.com/video/iGvvK8O5BpQ/v-deo.html
Check out this video ua-cam.com/video/iGvvK8O5BpQ/v-deo.html
Watch this video ua-cam.com/video/iGvvK8O5BpQ/v-deo.html
ua-cam.com/users/LearnGoogleSpreadsheetssearch?query=google%20sheets%20linking%20data
I would like to use what you have explained in this Video: “Google Sheets - Get Last Non-Empty Cell in Row or Column” The only additional thing is that I would like to sum up 2 cells in columns from 2 separate work sheets into my Dashboard Sheet. If this is possible, would you send me the formula lay out I need. I really enjoy your videos. I am still to expand my overall knowledge of google sheets. Thank you!
5:24 / 16:09
•
In the worksheet where you want the number there
type in the = then go to your worksheet and press the cell then enter and done
How can we display the last record of the google sheet inside the google form whenever we open the google form?
you can't.
How if we want to pick the second last item?
why not just use the if formula, if(row="",0,row(x))
why does it show in 1 and 0, when u had "--" , what does "--" mean?
Hi, thank you for the video. Is there a way to get this to work for a range on another sheet? I've tried using importrange and keep getting an error for the COLUMN or ROW function.
ROW & COLUMN require a range, IMPORTRANGE returns an array.
You could use SEQUENCE function to generate similar results to ROW or COLUMN, but it could be tricky if you use open references.
you are the best
Can you have multiple ArrayFormula in the same formula, example: =ARRAYFORMULA(INDEX('Form Responses 2'!A2:AN2,ARRAYFORMULA(MAX(COLUMN('Form Responses 2'!K2:AN2)*(--('Form Responses 2'!K2:AN2""))))))
You can have multiple array formulas, but you can't have nested arrays. Having several ARRAYFORMULA is the same as just doing one ARRAYFORMULA on the top level.
That being said, INDEX function in Google Sheets doesn't handle arrays. It does in Excel. In this case your MAX shouldn't return an array, so it wouldn't matter.
@@ExcelGoogleSheets But since the ArrayFormula is affecting in different ways in different spaces, it wont be the same, unless the whole formula has been tweaked right.
I found this formula working at last,
ARRAYFORMULA(IF(LEN(J2:J), VLOOKUP(J2:J,QUERY(SPLIT(FLATTEN('Form Responses 2'!K1:AN1&"_"&'Form Responses 2'!K2:AN),"_"), "Where Col2 ''", 0), 2, 0),))
It's not clear to me what you're trying to accomplish, so I have no idea 🙂
how get row number active cell automatic
?
Thanks!
Thank You!