Matt, I'm So glad I found your postings! I've been using excel for over 25 years and consider myself moderately advanced as compared to the majority of users. Your TH-cams are totally awesome! I only wish I were 25 years younger today() !!!
This is EXACTLY the formula I've been looking for. Took me a few weeks just to find a video of what I was looking for. I coped this exactly and for some reason its not working. All I need is the formulas up to 5:30. Once I add the MATCH function I just keep getting an error.
I can't seem to get it. I activated the CSE function correctly. Unfortunately it just creates zeros and doesn't move the names up on the new list. I used the exact same formula you used in the exact same cells. Is there anyway you have this file available to download? Or could I get it in an e-mail?
Hi Matt... Thanks for your work on this array formula. I am attempting to condense a list of values that are greater than zero. In other words, condense a list of value without zeros. Can you help with this?
Hey Matt! I'm doing EXACTLY what youre doing and I just get the first "name" duplicated all the way down. I noticed 1/2 way through when you divided the formula by 1000 that, although I had the large numbers, all my small #'s were the same #...??? I'm using Excel 16. Would that be an issue? GREAT way of doing it and you explain everything very well. I watched the video over and over again just to make sure everything was right. Please lend a hand if you can. Thanks CP
*UPDATE* I have NO IDEA what I did different. But it's all good to go. *MIND BLOWN* You have no idea how long Ive been looking for this kind of function. I can breathe. I can sleep. I can get off this damn computer. lol Take care. AWE-SOME!
Hi Matt, Not sure if you're still replying to comments, but I ran into an issue while using your code. I have a table on sheet1 that starts off empty. When I input information into the first row, the second sheet with the formula in your video doesn't work. Interestingly, when I enter information into the second row of my table, your formula in my second sheet works, picking up both rows of my table (as it should). The formula doesn't seem to work when I only have 1 row in my sheet1 table - do you have any ideas as to what might be causing this?
Hi Matt, I found your video very helpful, this is exactly what i am trying to achieve except, my array contains values instead of text. Are you still using the email you mentioned in the video? Thank you.
Hi Matt, I could really use your help; I've created a spreadsheet using an IF function to find all the drivers who have driven over 5,000 miles, (the drivers with less than 5,000, the IF function leaves the cell empty). So I then have a list of all drivers with >5,000 miles but there are empty cells between. When I use your formula (which is an impressive one), hit Control+Shift+Enter and copy down, it copies the column with the names and blank cells between. Any help you could provide would be greatly appreciated, even if there is a way to consolidate it so the IF function is encompassed within the formula you provided.
Hi Matt, Thanks for your help - I'm trying to do this between sheets of a workbook. Are there any tricks I'm missing? I can't seem to get it working, I keep getting #NUM and #N/A errors
we can use the function counta($A$3:$A$39) to count the names and write the formula: =if(rows($c$3:c3)>counta($A$3:$A$39),"",index(...........the rest of the formula
Hi Paul I have one question , in my list function source field contain merge cells , so when I click on list icon it shows blank space as well, as follows "ABC DEF GHI" can you please advice how to remove this blank spaces
Hi thank you for this but excel is returning #N/A, if i highlight the formula and manually calculate now it returns the correct answer. How do i solve this ?
That is far too complicated. Use Google Sheets with the following formula, which will take you 30 seconds to do. =query({A3:A}," select * where Col1 '' ",0)
***** Google Sheets doesn't have strong (or any) support for array functions. It might be better to create an index, like if cell = "", row() , then do a vlookup on the index
Matt, I'm having the same issues as Clint. The formula you present works well for the first cell. However, any subsequent cell I get a #NUM! error. When I hit calculate now and have an array of numbers I can get the code to work, but I would prefer not to hard code anything in this.
Matt, I'm So glad I found your postings! I've been using excel for over 25 years and consider myself moderately advanced as compared to the majority of users. Your TH-cams are totally awesome! I only wish I were 25 years younger today() !!!
Thank you Jicbs, thats a very nice thing to say! I am glad you enjoyed them.
Love this technique. Very clever. Thank you for sharing.
This was brilliant, thank you so much!
Thats what I was looking for
This is EXACTLY the formula I've been looking for. Took me a few weeks just to find a video of what I was looking for. I coped this exactly and for some reason its not working. All I need is the formulas up to 5:30. Once I add the MATCH function I just keep getting an error.
its a CSE function - so make sure you enter it in with ctrl+shift+enter
+Matt Paul Gotcha. Ok cool. I'll give that a try and you know what I get. Thanks for taking the time to respond.
I can't seem to get it. I activated the CSE function correctly. Unfortunately it just creates zeros and doesn't move the names up on the new list. I used the exact same formula you used in the exact same cells. Is there anyway you have this file available to download? Or could I get it in an e-mail?
i may have a file somewhere. Want to email me and ill send it to you. xlsxgeek@gmail.com
+Matt Paul Just sent it. Thanks!!!
Hi Matt... Thanks for your work on this array formula. I am attempting to condense a list of values that are greater than zero. In other words, condense a list of value without zeros. Can you help with this?
Hey Matt! I'm doing EXACTLY what youre doing and I just get the first "name" duplicated all the way down. I noticed 1/2 way through when you divided the formula by 1000 that, although I had the large numbers, all my small #'s were the same #...??? I'm using Excel 16. Would that be an issue? GREAT way of doing it and you explain everything very well. I watched the video over and over again just to make sure everything was right. Please lend a hand if you can. Thanks CP
*UPDATE* I have NO IDEA what I did different. But it's all good to go. *MIND BLOWN* You have no idea how long Ive been looking for this kind of function. I can breathe. I can sleep. I can get off this damn computer. lol Take care. AWE-SOME!
Clint Parks glad to hear. Best of luck
Thank you so much! :D This helped me heaps
Hi Matt,
Not sure if you're still replying to comments, but I ran into an issue while using your code.
I have a table on sheet1 that starts off empty. When I input information into the first row, the second sheet with the formula in your video doesn't work. Interestingly, when I enter information into the second row of my table, your formula in my second sheet works, picking up both rows of my table (as it should). The formula doesn't seem to work when I only have 1 row in my sheet1 table - do you have any ideas as to what might be causing this?
How do you get this to work with columns instead of rows?
Hi Matt, I found your video very helpful, this is exactly what i am trying to achieve except, my array contains values instead of text. Are you still using the email you mentioned in the video?
Thank you.
Hi Matt, I could really use your help; I've created a spreadsheet using an IF function to find all the drivers who have driven over 5,000 miles, (the drivers with less than 5,000, the IF function leaves the cell empty). So I then have a list of all drivers with >5,000 miles but there are empty cells between. When I use your formula (which is an impressive one), hit Control+Shift+Enter and copy down, it copies the column with the names and blank cells between. Any help you could provide would be greatly appreciated, even if there is a way to consolidate it so the IF function is encompassed within the formula you provided.
Hi Matt,
Thanks for your help - I'm trying to do this between sheets of a workbook. Are there any tricks I'm missing? I can't seem to get it working, I keep getting #NUM and #N/A errors
we can use the function counta($A$3:$A$39) to count the names
and write the formula:
=if(rows($c$3:c3)>counta($A$3:$A$39),"",index(...........the rest of the formula
Hi Paul
I have one question , in my list function source field contain merge cells , so when I click on list icon it shows blank space as well, as follows
"ABC
DEF
GHI"
can you please advice how to remove this blank spaces
why not use =FILTER(A3:A,A3:A"")
Due to not existing in excel at the time of the video
you are truly Awesome .Thank you 💕💕
Wow, Thank you.
What we can do for last step ,insted of whole formula dragging down ?
good day, thanks for this, but how about the data is coming from another sheet. can you help about this? thanks
can you show us how to do the same thing with cells with 0 values instead of balnks?
i have values instead of text which formula i have to use?
i sent mail too can u please help me out regarding this issue
Hi thank you for this but excel is returning #N/A, if i highlight the formula and manually calculate now it returns the correct answer. How do i solve this ?
ctrl+Shift+enter, got it !! Thanks alot
Nice one.
This does not work if I use filters.
That is far too complicated. Use Google Sheets with the following formula, which will take you 30 seconds to do.
=query({A3:A}," select * where Col1 '' ",0)
Ben Liebrand YOURE TOO COMPLICATED !!!
Okay, why don't you just use =FILTER(A3:A,A3:A"") ?
Ben Liebrand GET OUT OF HERE BEN!
No problem. Have a great day!
Just playing! I'm not sure if that feature existed when I made this video
Can you do a similar thing in Google Sheets? Any help would be appreciated.
***** Google Sheets doesn't have strong (or any) support for array functions. It might be better to create an index, like if cell = "", row() , then do a vlookup on the index
Matt, I'm having the same issues as Clint. The formula you present works well for the first cell. However, any subsequent cell I get a #NUM! error. When I hit calculate now and have an array of numbers I can get the code to work, but I would prefer not to hard code anything in this.
hi, I am practising your formula but there are #N/A and #NUM showed in the cells I copied. Why?
Thank you.
Because you did not do a CTRL+SHIT+ENTER after inputing the formulas.
Before filter function too much work
what is your email address