LinkBack URL
About LinkBacks
Hi all,
Is there a non-array way to return only the non-blank cells from a range?
At the moment I use below formula, but it slows down the file considerably
Please Login or Register to view this content.
Hi all,
Is there a non-array way to return only the non-blank cells from a range?
At the moment I use below formula, but it slows down the file considerably
Please Login or Register to view this content.
filter, then copy paste
or even.
go to/special blanks and delete
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hello Martin,Originally Posted by martindwilson
Problem is that the data is the result of several parameters and is continually changing.
So I need a formula for this
try this array formula
=if(row(1:1)>counta($D$4:$D$50),"",INDEX($D$4:$D$50,MATCH(,COUNTIF($E$2:E3;$D$4:$D$50),)))
Try this (no need to confirm with Ctrl+Shift+Enter):
Excel 2003:
=IF(ISERROR(INDEX(D:D,SMALL(INDEX(NOT(ISBLANK($D$2:$D$20))*ROW($D$2:$D$20),0),COUNTBLANK($D$2:$D$20)+ROW(D1)))),"",INDEX(D:D,SMALL(INDEX(NOT(ISBLANK($D$2:$D$20))*ROW($D$2:$D$20),0),COUNTBLANK($D$2:$D$20)+ROW(D1))))
Excel 2007:
=IFERROR(INDEX(D:D,SMALL(INDEX(NOT(ISBLANK($D$2:$D$20))*ROW($D$2:$D$20),0),COUNTBLANK($D$2:$D$20)+ROW(D1))),"")
Last edited by pb71; 07-04-2010 at 02:18 PM. Reason: Excel 2003 formula +ROW(D2) corrected to +ROW(D1) and example workbook attached
Awesome, thanks PB!
Last edited by Cutter; 10-29-2012 at 04:33 PM. Reason: Removed whole post quote
Kick ***!
Thanks for posting this, I was hitting plenty of dead ends trying to figure this out myself with Index-Match functions.
Best
Andy
Hi Andy. Could you please tell me what is the Index-Match formula that you came up with as I am looking for the same thing ?
Thanks
Dany
Deleted post.
Started new thread.
Last edited by Mecha_Trueno; 05-30-2013 at 08:48 AM.
Mecha,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Attachment 279560
Hi pb71,
Thank you for the solution, but this doesn't seem to work for larger ranges with more blanks. Can you take a look at my attached file and let me know if you have a new solution?
Thanks,
Doug
douge1238,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
arlu1201,
It's not really a new question if the "solution" doesn't work...
Sometimes the solution you require could be completely different than the original posters solution, hence we encourage to post in a new thread to avoid confusing other users who read this thread.
Hi Andy,
I need yor help, if we have more than one column means how can i put formual. Excel attached.
Thanks in advance,
Guna.S
Last edited by sguna1982; 06-04-2014 at 08:07 AM. Reason: Attachment missing
This is what I've been looking for...
But no one could solve the problem yet....
My brain will explode thinking about this...
Help us please !
I realize that this is an old post, but I've just had a similar need to return all non-blank cells of a row. However, instead of the value in the cell, how may I make use of the INDEX function to return the header row???
Please start your own thread.
The solution is relatively easy!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you for the file
Thanks bro
Thanks Bro
Mr. Manish - what is the purpose of your posts? Whom are you thanking and why?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks