• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula to Remove Blank Cells from a Range

Harry0

Harry0

Member
The sort function at the top is useless on certain things since sometimes things have to be sorted automatically from a wide or long range of randomly filled out rows.
In short to have a secondary column that puts in order the first column.

one site says to use this
=INDEX($B$3:$B$20, SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", ROW($B$3:$B$20)-MIN(ROW($B$3:$B$20))+1), ROW(1:1)))

=IFERROR(INDEX($A$2:$A$9, SMALL(IF(ISBLANK($A$2:$A$9),"", ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1), ROW(A1))),"")

As the first one states $B$3:$B$20 and iff you make it without the "$" like B3:B20 then it does not work. If you move it to another column $A$3:$A$20 then it does not work. Even if i use the same formula on a different page it sometimes does not work. Magic?

But both are inconstant when moving it to another row or cell since they give error despite moving them properly across. And sometimes does not even work well or at all.

I am using the crappy Mac of 2011 excel with the latest updated version.
 
Here you go file attached.
J Can't sort row I.
Copied formula and removed $ and won't work.
Used other formula and did not edit it and won't work.
It was taken from another site and edited. after column G.
 

Attachments

  • excel_dynamic_dataval_excl_blanks.xlsx
    26.2 KB · Views: 7
@Harry0

See your file, The formula you entered in Column J&K are array formula which must be executed with Ctrl+Shift+Enter and you enter them with just Enter. See them now. As far as column N formula is concern, I am not sure what you want out of it.

Regards,
 

Attachments

  • excel_dynamic_dataval_excl_blanks.xlsx
    13.6 KB · Views: 4
Thank you
Column N did not work when I had it but it seems you added {}.
So all Ctrl+Shift+Enter does it put {} in it, but you can not put it manually it seems.

I was doing it with another formula that did not use the Ctrl+Shift+Enter but used a helper column

Which column A is (helper)
=IF(COUNTIF(CZ$3:CZ6,CZ6)=1,CY5+1,CY5)
column B is (disorder)
=IF(AND('Full List'!$B3=$C$1,'Full List'!$L3=CZ$3),'Full List'!$A3,"")
And column C (result)
=IFERROR(VLOOKUP(ROW()-4,CY$5:CZ$1600,2,FALSE),"")

This site references it, despite I got it from another site.

Extracting Unique, Duplicate and Missing Items using Formulashttp://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/
I assume there is no way to do this without a helper column, or is there a way?
Thanks
 
@Harry0

{...} is inserted by Excel around functions when you tell Excel that you are trying to do an array operation on a function which don't expect array. for e.g.

IF function, normally IF function logical part is where you pass a single cell ref., but when you pass a range like =IF(A1:A20 = "Yes"...... now here you are passing an array in logical part which expect a single value argument, in such situations you have to tell Excel to operate on this as an array. This is done by Ctrl+Shift+Enter. I will suggest you to read some articles on Array formulas that is there on Chandoo's blog or any other blog.

Regarding you next query, I did not understood , can you upload a sample file related to it.

Regards,
 
@Harry0

If you talking about the example on the link, than see the attached file, it uses a bit complicated array formula (if you are a starter in array formulas) it don't uses helper column.

Regards,
 

Attachments

  • unique-duplicate-missing-items.xls
    38.5 KB · Views: 5
Ok see sheet 2
I tried out the array and it is not working now on Column N. It is just duplicating column C.
Now what am I doing wrong?

Column D not being sorted in order as it should be, but it is removing blanks . Helper column is row B. Sorry I messed up that one in the ranking but it was used to compress.
Thanks
 

Attachments

  • excel_dynamic_dataval_excl_blanks-2.xlsx
    122.2 KB · Views: 2
Last edited:
@Harry0

See the file, You column C data where you were seeing the blank cells, were not actually blanks, if you press F2 on a blank cell and just press enter you will see that the blank rows from the result of formula in column N will go away. Repat the same for each blank cell in column C.

Now I inserted a non-array formula in column P to give you a list of sorted number. But this will work on numbers only.

Regards,
 

Attachments

  • excel_dynamic_dataval_excl_blanks-2.xlsx
    121.7 KB · Views: 2
Thanks
Column P is a very good trick to sort and remove blanks.

As for the original, is there a way to keep the formulas on a blank cell and have your formula remove blanks?
I need column C to be as it is since it gets it from another workbook.

I am working on one confusing mess. which I am trying to get it in a somewhat order but in the end it will have a lot of formulas as you already see since I use a lot of helper columns which will not make it that efficient but I am trying.
expect more posts since this is driving me nuts. LOL
 
So I am assuming you will get data as per column C and you want a formula to sort it. See the file, I had not altered your data of column C and column P formula still holds good.

Regards,
 

Attachments

  • excel_dynamic_dataval_excl_blanks-2 (1).xlsx
    124.3 KB · Views: 7
I tried out the formula and it missed some numbers sometimes.

here is a link to another file which messed up the thing http://chandoo.org/forum/threads/automated-every-x-weeks-with-offset-weeks.19755/
page code on g and w4x T and some other ones. I have a lot of formulas around trying to figure it out that and other things and nothing is working.

Sorry but my file is bloated which takes a while to process since I do not know hot to make it efficient. Also if you download the file disable macros which I do not know what that is.

update: I assume this sort compress array might work. yet it gives an error of circular reference when removing or adding numbers.
=INDEX($K$5:$K$358, MATCH(SMALL(IF(ISBLANK($K$5:$K$358), "", IF(ISNUMBER($K$5:$K$358), COUNTIF($K$5:$K$358, "<"&$K$5:$K$358), COUNTIF($K$5:$K$358, "<"&$K$5:$K$358)+SUM(IF(ISNUMBER($K$5:$K$358), 1, 0))+1)), ROW(A1)), IF(ISBLANK($K$5:$K$358), "", IF(ISNUMBER($K$5:$K$358), COUNTIF($K$5:$K$358, "<"&$K$5:$K$358), COUNTIF($K$5:$K$358, "<"&$K$5:$K$358)+SUM(IF(ISNUMBER($K$5:$K$358), 1, 0))+1)), 0))
 
Last edited:
Hi Harry,

I think you should upload a sample file with the data and required output in manually no formula.

Pointing to different formulas will only create confusion and will not lead to any solution.

If you see the other thread which you posted today, some 27 views have been there, but no reply. The file is so confusing, you should have removed all the not required sheets from the file and would have stated the ref. where you want formula and which all cells would be in relation to this formula.

The forum excepts a file size of max 1 mb.

Regards,
 
page 1 is the contact list
page 2 is the print out
page 3 is the calander
 

Attachments

  • contact temp-2.xlsx
    67.9 KB · Views: 3
Harrrrrryyyyy, sheet names I can see in the file. :) Can you explain in respect to this file what are you looking after?

Regards,
 
Well in more details a automated schedule or 1000 and growing locations to be visited by driver in location order by day on a weekly basis. Sometimes 50 places are visited by day and some of those places will be seen again within a week, 2 weeks, 3 weeks, etc. While the next wee only the weekly ones stay and some other places will be visited every 2 weeks, 3 weeks, etc. Sometimes some places have to have other reminders every few weeks or months or 2 months.

Many times places have to be revisited again the next week due to lack of time. And sometimes the place needs to be visited on other days or by other drives. So from a daily list of 50 places it can be confusing on where to go first which is why I merged latitude and longitude while referencing zip to kind of reference which is best to go first.
To be able to change the offset week quickly out of 1000 places is tricky which I always got circular calculation errors. Even the calculation I used has a limit since when 2014 ends then it will not work as orderly since I was basing the automated thing on the week of the year and it would restart when going to 2015.

I lost track of order and sometimes forgot what I was doing due to the confusion, and the formulas not working confused things even more.
So the first place is a growing list, second page is the 5-6 day schedule to be printed out which is why I needed to sort and put together in a page, and the third page is to see on the calendar as a record to keep track to be also printed just in case things mess up.

At work I tried to do it on excel 2011 but at home I tried to put it on excel 2003 and the file got bloated to convert and many formulas did not work. But I do not need it to work at home which explains why the file was big at 13mb while the excel 2011 was 3mb. Whatever the case the file was bloated and went slow and sometimes made the computer freeze.

Hope that explains things.
Thanks
 
Ok I got what you have written up, but since you did not relate to the file of comment #14. I could not understood what is your requirement.

Regards,
 
sorry I thought I did. But everything I last stated is what I need which I tried to do which did not fuction or fully come out in the other post which I made ablank example in #14 and explained it all in what I was trying to do in the last reply on #16. Sorry if I did not explain again what you asked but I tried to explain what I assume you mean.
 
You must log in or register to reply here.
Back
Top