Array 7: Formula to get rid of blanks
HTML-код
- Опубликовано: 15 сен 2024
- Find me on Twitter here: / mattpaul25
I show you a formula method for a taking a list with blanks - and condensing it to a list with no blanks
Make sure you hit CTRL SHIFT ENTER
Also i use sum(iferror(search(,range),0) over COUNTA because counta will count cells with "" values. So it wont count blank cells like i was showing here - but it will count cells that have a "" value. It is interesting how excel distinguishes between the two. COUNTA will work in this example however
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 RUclipss 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!
How do you get this to work with columns instead of rows?
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 ?
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?
good day, thanks for this, but how about the data is coming from another sheet. can you help about this? thanks
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.
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 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?
can you show us how to do the same thing with cells with 0 values instead of balnks?
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
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
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
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 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
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.
Thank you so much! :D This helped me heaps
Thats what I was looking for
This does not work if I use filters.
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.
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.
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
Before filter function too much work
Nice one.
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
what is your email address