Remove blank cells
In this blog post I will provide two solutions on how to remove blank cells and a solution on how to remove blank rows:
 Remove blank cells [array formula]
 Remove blank cells [keyboard shortcut F5]
 Remove blank rows [array formula]
Remove blank cells [array formula]
Column B is the list with random blank cells. Column D is the list without the blank cells.
Array formula in cell D3:
Recommended article
Delete blanks and errors in a list
The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they […]
Delete blanks and errors in a list
How to create an array formula
 Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.

Press and hold Ctrl + Shift.
 Press Enter once.
 Release all keys.
Array formulas allows you to do advanced calculations not possible with regular formulas.
How to copy array formula
 Copy (Ctrl + c) cell D3
 Paste (Ctrl + v) array formula on cell range D3:D8
Remove #num errors (excel 2007)
When you run out of values to show, the array formula above returns #NUM! errors. You can avoid this if you use the IFERROR function, however use it with great caution. It not only finds #NUM! errors but all errors. So if you formula or cells contain an error you won't see it, the IFERROR function removes that error too.
You can read more about the IFERROR function here:
How to use the IFERROR function
If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]
How to use the IFERROR function
Recommended article
Remove blank cells [keyboard shortcut F5]
The image above shows random cell values in column B, follow these simple steps to remove blank cells in column B.
 Select range B2:B12.

Press F5 and a dialog box appears.

Click "Special..." button.
 Click radio button "Blanks".

Click OK button. The image below shows that the cell range selection changed, now only blank cells are selected.

Rightclick on one of the selected blank cells and a context menu appears, select "Delete..".

Another dialog box appears, click "Shift cells up".
"Shift cells up" will delete selected blank cells and move non empty cells up. This step will mess up your dataset if you have values arranged as records.
"Entire row" will delete row 3, 6, 8 and 11 in image above. If you have data on these rows they will be deleted as well.  Click OK button.
The image above shows that blank cells are now deleted.
Remove blank rows (array formula)
Array formula in cell E2:
How to create an array formula
 Select cell E2
 Paste formula
 Press and hold Ctrl + Shift
 Press Enter
How to copy array formula
 Select cell E2
 Copy cell (Ctrl +c)
 Select cell range E2:E10
 Paste
How to find errors in a worksheet
Excel has great builtin features. The following one lets you search an entire worksheet for formulas that return an error. […]
Delete blanks and errors in a list
The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]
I would like to use exactly this function, but I cannot get your example to work. Why do you have semicolons in the formula?
It has to do with regional settings. UK and US have colons, and we use semicolons. I will soon change all formulas to UK/US settings.
I do not know why but wordpress changes "" (double quotation marks) to
[...] G1:G16 is where I create the unique list. The downside is that there are blanks where a duplicate is found. See this article on how to remove blanks: Remove blank cells [...]
[...] Filed in Uncategorized on Mar.20, 2009. Email This article to a Friend In a previous article Remove blank cells, I presented a solution for removing blank cells. This only worked for cells containing text. In [...]
How can I use these formulas in case if cells are only virtually blank, i.e. "" values are retruned in particular cells by IF functions? Is there any way to remove such cells from the list?
Alex,
This formula removes cells that seem to be blank but contains a space character:
=INDEX($A$1:$A$8, SMALL(IF(TRIM($A$1:$A$8)="", "", ROW($A$1:$A$8)MIN(ROW($A$1:$A$8))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as needed.
This is excellent. Thank you!
Can you think of any way to do this within a Named Range?
E.g., so I can define a range called "FilteredList" which only contained the cells with values, and then refer to that list elsewhere in the sheet?
thanks!
(sorry: I should have been clearer. I want to do it with a named range only  without creating a hidden sheet containing the filtered list or anything like that.)
Greg,
I am using the same example as in this blog post and Excel 2007.
1. I copied this formula
=INDEX($A$1:$A$10,SMALL(IF(ISTEXT($A$1:$A$10), ROW($A$1:$A$10),""),ROW(1:10)))
2. I created a new named range (rng) in the "Name Manager" and pasted the formula into the "Refers to:" field.
3. Click OK!
3. I then selected a new range (D1:D9) and typed in formula field: =rng + CTRL + SHIFT + ENTER
The result were a list without blanks. I have never tried this before but it seems to work.
Hi! can you do this using the transpose function? so when you transpose a column to a row it removes the blanks in the process?
Thanks!
Instead of the column to a row, do a row to a column.
Arielle,
=INDEX($E$1:$K$1, 1, SMALL(IF(ISTEXT($E$1:$K$1), COLUMN($E$1:$K$1)MIN(COLUMN($E$1:$K$1))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.
That is great thanks!! it works perfect!!! Now is it possible to do that exact formula to ignore cells that have the virtual blank ""?
not ignore i mean remove im sorry
Arielle,
=INDEX($E$1:$L$1, 1, SMALL(IF(LEN($E$1:$L$1), COLUMN($E$1:$L$1)MIN(COLUMN($E$1:$L$1))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.
Oscar,
unfortunately that did not work :/. It did the same thing as the previous formula you gave me, which works great, but the new formula isn't removing the cells that have a space in it
Arielle,
Now I understand, try this formula:
=INDEX($E$1:$L$1, 1, SMALL(IF(LEN(TRIM($E$1:$L$1)), COLUMN($E$1:$L$1)MIN(COLUMN($E$1:$L$1))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.
thanks that works great!!!!!
I have extracted a unique list from a list containing blanks ("LIST" is the named range), using this formula in Excel 2007:
=INDEX(LIST, SMALL(IF(TRIM(LIST)="", "", ROW(LIST)MIN(ROW(LIST))+1), ROW(1:1)))
This works beautifully, except for one small thing. I need the unique list to display within a set number of rows, however the range "LIST" varies in size. I do not want #NUM! to display in the cells below the unique list. How can I make the cells below the unique list look blank, rather than displaying #NUM! ?
Laura,
=IFERROR(INDEX(LIST, SMALL(IF(TRIM(LIST)="", "", ROW(LIST)MIN(ROW(LIST))+1), ROW(1:1))),"") + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.
Oscar, I just changed your semicolon (near the end of the formula) to a comma, and it worked PERFECTLY. Thank you for your help!
This is awesome!! I have been searching the internet looking for just this. One question though why does this not work?
=IF(SUM(IF(A1:A10"",1,0))>=ROW(),IF($V$4:$V43="space","",INDEX($V$4:$V43,SMALL(IF(TRIM($V$4:$V$43)="","",ROW($V$4:$V$43)MIN(ROW($V$4:$V$43))+1),ROW(1:43)))),"")
Basically i took the formula that removes seemingly blank cells and tried to combine it will the one that removed the #NUM!?
The formula with out the beginning IF part works great.
Can this be done to remove the #NUM!? too?
Thank you!
Additional... i did make the initial IF statement have the array v4:v43 i just forgot to put that correct in the post. Thank you
Sam,
Read this: Delete blanks and errors in a list
Excel 2007:
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
Thanks for commenting!
I dont know why it is not working on me "+ CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed." What I do is just drag the formula down up to the last cell I need. I am not sure if it is correct.
I really dont know what is wrong please help thanks
JOshua,
I am not sure what is wrong.
$B$3:$B$10 is the cell range. Adjust range to your sheet.
A1 (bolded) is a relative cell reference. This cell reference changes when you copy the formula.
Yes, you can also copy the formula by click and hold and drag the cell down to the last cell you need.
Wondering if you can help.
Easiest to put an example down:
CURRENT TRYING TO DO WOULD BE IDEAL
A B A B A B
Name 1 Data 1 Name 1 Data 1 Name 1 Data 1
Name 2 Name 3 Data 2 Name 4 Data 1
Name 3 Data 2 Name 4 Data 1
Name 4 Data 1 Name 6 Data 3
Name 5
Name 6 Data 3
Where the name stays matched up with the data item in the same row. I would think using hlookup or vlookup in some fashion might help but I can't wrap my brain around it. Any thoughts?
Well that doesn't look pretty how about this:
CURRENT TRYING TO DO WOULD BE IDEAL
ABABAB
Name 1...Data 1Name 1...Data 1Name 1...Data 1
Name 2...______Name 3...Data 2Name 4...Data 1
Name 3...Data 2Name 4...Data 1
Name 4...Data 1Name 6...Data 3
Name 5...______
Name 6...Data 3
Basically I'm looking to do the same thing the filter does but what I want to end up with is a single sheet that has 10 of these filterd lists above and next to each other.
Sorry  I found a work around using your current formulas above, glad I found it! Going to take some doing to set it up but it will be worth it.
Thanks!
Great to see a nonvolatile solution to this problem.
If the apparently empty cell is "" returned by a formula then the "blanks" will not be removed,in fact as they are all read as different "blanks" and the list can/will remain unchanged.
This mod seems to handle this situation.
=INDEX($B$2:$B$200, SMALL(IF(($B$2:$B$200="")+ISERROR($B$2:$B$200), "", ROW($B$2:$B$200)MIN(ROW($B$2:$B$200))+1), ROW(1:1)))
Wrap in IFERROR("formula","") for 2007 and above, or IF(ISERROR("formula"),"","formula") for 2003 and earlier.
Marcol,
Yes you are right. ISERROR($B$2:$B$200) removes formulas that return an error.
[...] how it can be done using formulas. One of them that explained how it can done can be found a https://www.getdigitalhelp.com/2007/09/16/excelremoveblankcells/ modified the formula to do it for columns. Thennbsp;I setup the formulanbsp;such thatnbsp;he [...]
Oscar,
Thank you sooo much~!The formula removes cells that seem to be blank but contains a space character solve all my problems~!
Thank you SO MUCH for this!!!!!!!!!! I've been trying to write if statements for the last 2 days to try to filter out unneeded data. I just converted the garbage to blank cells then used your equation..... ITS Beautiful!!!!!!!
Thanks so much!!!
[...] available options and did not have blank spaces between them. I hunted on the internet and found an example, but it used functions that are not available in the Xcelsiusenabled list of Excel [...]
Hi Oscar,
I read your blog almost everyday. I found a lot of solution for interesting task here. I want to ask you for help. I tried to find solution for removing blank cells not in one column, but in range with few columns. Because the range is dynamic it possible to be from 1, 2, 3 or more columns.
I have a range similar like this:
A B C D E F
1 x x
2 x x
3 x
4
5 x
6
7
8 x x
The result must be:
1
2
3
5
8
Do you think that is possible without VBA?
One way might be to try this variation with the aid of a helper column.
Add two columns before Column A in your worksheet, or start your data table in Column C. Put as many as many headers as you need in Row 1 (These must be text strings for this example, dates as headers require a slightly different formula)
In B2
=IF(C2="","",COUNTIF(D2:INDEX(2:2,1,MATCH(REPT("z",255),$1:$1,1)),">"""))
Drag /Fill Down as required
In A2, this Array formula
=INDEX($C$2:$C$200, SMALL(IF(($C$2:$C$200="")+ISERROR($C$2:$C$200)+(($B$2:$B$200=0)), "", ROW($C$2:$C$200)MIN(ROW($C$2:$C$200))+1), ROW(1:1)))
Confirm with Ctrl+Shift+Enter, not just enter
Drag /Fill Down as required
Column B is a count of your "x" "flags", it could be hidden.
If your "flag" headers are serial dates change this
"REPT("z",255)" to "99^99" in B2.
Sorry, should have added this to the above post ...
Wrap (A2) in IFERROR("formula","") for 2007 and above, or IF(ISERROR("formula"),"","formula") for 2003 and earlier.
Hi Marcol,
With helper column is easy :) In fact with using helper formula the task is the same  "Remove empty cell ls in one column (helper)". I wonder if there is way to find nonblank lines from multi column table.
In fact yesterday I found way to do that, but the way is not so good in my opinion. I mean that if table is with a lot of columns it will be not possible to use the formula. I tried to made the formula unique  for 2, 3, 4 or more columns, but in the beginning it works for 2 or 3 columns, but for 4 failed to give results. I made some modifications and the formula start to work for 2, 3 or 4 columns... But the number of columns is still limitation  if I need to check more than 4 column table I need to integrate in formula more IFs (the same number as columns).
On Monday I will post the formula to see what I mean.
@ BatTodor
I'm not sure that I'm following you.
See if you can download this sample file from my skydrive.
https://skydrive.live.com/?cid=1760EAB0F9AE526F#!/view.aspx?cid=1760EAB0F9AE526F&resid=1760EAB0F9AE526F%21125
Add as many column headers as you want/need, then put "x" in any row in any column.
Hi Markol,
Thank you for solution! :)
I will try to find solution without helper column :)
BatTodor ,
I am not sure I understand.
What is x in your table?
Can you provide the formula you are working with?
Hi Oscar,
In general I want to know all rows which are not empty. "X" mean that (for example) number 1 is appeared in Column B and C. The task is became more complicated because I work with dynamic ranges and it could be one range to be with 1 or 2 or 3 or more columns. I wanted to create one formula for all cases – even the range grows to 10 or more columns. I modified your formula with integrated IFs, but for some strange reason formula failed to give results in some cases for 3 or 4 columns (for now the cases is with maximum 4 columns). After that I modified again formula and put one more IF – if there is 2 or more columns.
The final formula looks like this:
=INDEX(AllRanges;3+SMALL(IF(ISBLANK(INDEX(SelectedRange;;1)); IF(ISBLANK(INDEX(SelectedRange;;2));IF(COLUMNS(SelectedRange)>2;IF(ISBLANK(INDEX(SelectedRange;;3));IF(ISBLANK(INDEX(SelectedRange;;4));"";ROW(INDEX(SelectedRange;;4))MIN(ROW(INDEX(SelectedRange;;4)))+1);ROW(INDEX(SelectedRange;;3))MIN(ROW(INDEX(SelectedRange;;3)))+1);"");ROW(INDEX(SelectedRange;;2))MIN(ROW(INDEX(SelectedRange;;2)))+1); ROW(INDEX(SelectedRange;;1))MIN(ROW(INDEX(SelectedRange;;1)))+1); ROW(1:1));3)
I have 2 worksheet – in Worksheet1 is database and Worksheet2 is for calculations.
Where AllRanges is
=OFFSET(Worksheet1!$A$1;0;0;COUNTA(Worksheet1!$C:$C)+2;COUNTA(Worksheet1!$1:$1))
SelectedRange is
=OFFSET(Worksheet1!$A$1;3; Worksheet2!$G$21;COUNTA(Worksheet1!$C:$C)1; Worksheet2!$F$2 Worksheet2!$G$2+1)
On Worksheet2 on cell C4 there is cell for choosing. From this choose SelectedRange is defined.
Again on Worksheet2 on cells F2 and G2 there are array formulas to calculate last and first columns for the range which was choosen
=MAX(IF(Worksheet1!3:3=$C$4;COLUMN(Worksheet1!3:3)))
=MIN(IF(Worksheet1!3:3=$C$4;COLUMN(Worksheet1!3:3)))
I hope that I was able to describe situation :)
Ups, I saw that the formula is not clear for reading :(
=INDEX(AllRanges;3+SMALL(IF(ISBLANK(INDEX(SelectedRange;;1)); IF(ISBLANK(INDEX(SelectedRange;;2));IF(COLUMNS(SelectedRange)>2;
IF(ISBLANK(INDEX(SelectedRange;;3));IF(ISBLANK(INDEX(SelectedRange;;4));"";
ROW(INDEX(SelectedRange;;4))MIN(ROW(INDEX(SelectedRange;;4)))+1);
ROW(INDEX(SelectedRange;;3))MIN(ROW(INDEX(SelectedRange;;3)))+1);"");
ROW(INDEX(SelectedRange;;2))MIN(ROW(INDEX(SelectedRange;;2)))+1);
ROW(INDEX(SelectedRange;;1))MIN(ROW(INDEX(SelectedRange;;1)))+1); ROW(1:1));3)
hi, thanx 4 your valuable formulas
i have a query, if u help i will be very thankful....
i have a workbook which is having 2 worksheet
i sheet1 i have a data range C1:H100
i just want a formula with this criteria
if i type "OK" in any column rang from J1:J100
the same row which cell contains "OK" match with other 6 rows range
and return the data in sheet2 with ignoring the blank calls
the data should be return in first row
and it should follow the same criteria when in put "OK" in any other column eg:
Sheet1 sheet2
C D E F G H J C D E F G
1 4 5 2 11 "OK" 1 2 4 5 11
please help me for this
i will be very thankful to u all
This is an amazing piece of code.
8)
BatTodor,
I modified the formula in this post:
Unique distinct values from multiple columns using array formula
This formula works with blanks.
SUNNY,
the same row which cell contains "OK" match with other 6 rows range
Can you explain in greater detail?
Matt Villion,
thanks!
Hi Oscar,
Thank you very much for your reply, but the formula which you send me isn't give result in my case...
It's my mistake  sorry for my poor English :( In my example the digits on first columns are the rows numbers, not entered digits.
The task looks like this "Remove blank rows from multicolumn array". I want to know which rows contains data (I marked with "X") on one or more cells (columns).
Hi Oscar,
thanx for giving me ur valuable time
after one day if mind exercise i came to conclusion and this formula works for me but it slowdown my excel file processing.
if possible can you help for the above mention problem
{=IFERROR(IF(V4="OK",INDEX(K4:O4, SMALL(IF(ISBLANK(K4:O4), "", COLUMN(K4:O4)MIN(COLUMN(K4:O4))+1), COLUMN(A3))), ""),"")}
thanx again
BatTodor,
Check out this formula:
https://www.getdigitalhelp.com/wpcontent/uploads/2007/09/Removeblanksfromacellrange.xlsx
Unfortunately it is a complicated formula, I wish I could make it smaller.
hi oscar,
is there any formula that can replace a rang of cells value into text
eg:
A B C D E W
1 3 4 6 8
2 4 5 7 9 OK
1 2 6 8 3
now i want to replace the value 1 with (oscar)
is it possible with formula.
one more question is that:
is that any formula which can lookup the cell rang W2:W321 & if any cell contains "OK" then it lookup the cell rang in the same row i.e. A:E
and then count the value just like in example.
thanks in advance
Hi Oscar,
THANK YOU VERY MUCH for solution! :) In fact for my current task which I try to solve is enough to know the numbers of nonempty rows and this part of your formula gives needed information =SMALL(IF(FREQUENCY(IF(List"";MATCH(ROW(List);ROW(List));"");MATCH(ROW(List);ROW(List)))>0;MATCH(ROW(List);ROW(List));"");ROW(A2)). In any way it will be big challenge for me to understand the formula :)
In general for me is interesting to know the way for creating array formulas – how to trace results, how to choose when use array formula "rowbyrow" instead "forallarea" (I'm not sure that I wrote it right), etc. I read your blog often, but the logic for array formulas is very big challenge for me.
In the past it was like a magic – now I see that you started to explain very detailed the reason of using parts of formulas :) And now it comes to cleared the picture, but still is hard for me to create my own array formulas.
In any way again want to thank you Oscar!
SUNNY,
You can´t replace a value in another cell using a formula. A formula can only return a value in the cell it is entered.
and then count the value
Can you explain in greater detail?
The best one:
G14:G19 is the array with blanks and nonblanks. Put it in the first target cell in the column of results. With cursor in formula bar, press Control+Shift+Enter. Now drag it along the entire range.
johnson,
thanks for sharing!
Thank you Oscar,
Your approach is great and works fine. I used several hours to find such a solution.
Keep on sharing the knowledge.
Idrissa
Idrissa,
thanks for commenting!
Hello,
I am having the same issue as above with a little twist. Have 4 active wsorksheets in a workbook. Each worksheet has formulas that pull data from the another worksheet. What I need to do is combine one formula with another formula that will pull over the data as well as delete any blank rows. The formula that I have which is pulling over data is: Column A: =IF(Modifications!C4="Open / Active","A",IF(Modifications!C4="Completed","D","")). Modifications being one of the active worksheets. Column B: =IF(Modifications!A4="","",Modifications!A4). Currently, its pulling the data correctly but there are blank rows, I need those deleted and can't figure out how to combine the formulas. Please help!!
BTW, I am comparing a current worklist with a new worklist that will let me know what I need to upload as an Add or Delete.
Lisa S,
See attached file:
Removeblankrows.xlsx
Thank you for the great work Oscar!
I found your remove blank rows array formula to be especially helpful. Is it also possible to add another condition onto that? For instance, using your example, would you be able to remove blanks and list only the rows of people over the age of 30?
Thank you for your help!
Yen,
Thanks!
Is it also possible to add another condition onto that? For instance, using your example, would you be able to remove blanks and list only the rows of people over the age of 30?
Yes, it is possible!
Array formula in cell E2:
Thank you Oscar! This formula is exactly what I've been searching for. I just wanted to thank you for the excellent resource you are providing!
Thank you for commenting!
Oscar, thank you so much! I had a chance to test your solution and it works beautifully.
I had no idea about the option to use * in an IF function to accommodate multiple conditions. This will definitely make my life a bit easier :)
I'm going crazy trying to figure this one out...
Sorry to bring you back to the olddays of yore, but I'm still using Excel 2003... (don't laughit was free!)
I'm, as you can guess, trying to remove blank lines. I haven'ttried allof the formulas on the >https://www.getdigitalhelp.com/2007/09/16/excelremoveblankcells/ =INDEX($B$3:$B$10, SMALL(IF(ISBLANK($B$3:$B$10), "", ROW($B$3:$B$10)MIN(ROW($B$3:$B$10))+1), ROW(A1))) =IFERROR(INDEX($B$3:$B$10, SMALL(IF(ISBLANK($B$3:$B$10), "", ROW($B$3:$B$10)MIN(ROW($B$3:$B$10))+1), ROW(A1))), "") <)
There are three problems with these formula (actually, with me):
(1) I don't know how to modify these formulas to my range (A59 to I129)
(2) This formula creates a #NUM error in the resulting array if the cell in the source array is blank. I don't know how to modify the formula to show blank cells instead of an error. (Yes, I want to show blank cells, not #NUM error indicators.)
(3) I don't know how to modify the second formula to Excel 2003.
Can someone help?
Thanks
hendis
hendis,
This formula should work in excel 2003:
=IF(ISERROR(INDEX($B$2:$C$9, SMALL(IF(FREQUENCY(IF($B$2:$C$9<>"", MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)))>0, MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), ROW(A1)), COLUMN(A1))), "", INDEX($B$2:$C$9, SMALL(IF(FREQUENCY(IF($B$2:$C$9<>"", MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)))>0, MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), ROW(A1)), COLUMN(A1)))
Using your cell references:
=IF(ISERROR(INDEX($A$59:$I$129, SMALL(IF(FREQUENCY(IF($A$59:$I$129<>"", MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)))>0, MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), ROW(A1)), COLUMN(A1))), "", INDEX($A$59:$I$129, SMALL(IF(FREQUENCY(IF($A$59:$I$129<>"", MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)))>0, MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), ROW(A1)), COLUMN(A1)))
See attached file:
Removeblankrowsfromacellrangeformulaexcel2003.xls
Oscar, thank you for the comment, formula and file.
(One look at the formula you put together tells me that there is no way in the world that I would  or could  have figured this thing out.)
Unfortunately, when I copy and paste your formula into my spreadsheet, I get an error. When I download and open the Excel 2003 file you included, the spreadsheet shows a VALUE error, but nothing else  no formula, no text, no nothing.
What am I doing wrong?
hendis
hendis,
My formula uses more levels of nesting than are allowed.
Try this formula:
See attached file:
Removeblankrowsfromacellrangeformulaexcel2003_2.xls
Hi Oscar,
I am trying to use your array formula
=INDEX($C3:$AO3,SMALL(IF(ISBLANK($C3:$AO3),"",COLUMN($C3:$AO3)MIN(COLUMN($C3:$AO3))+1),COLUMN(A1)))
on values collected using the CONCATENATE function, some of which are zeros. Most of the cells in my row arrays don't return a value but contain the concatenate formula and therefore aren't removed by the above.
Can ISBLANK or another part of the formula be easily adapted to remove cells that don't return a value (showing those that return a zero) but do contain a formula.
Look forward to hearing from you,
Matt
Matt,
Try this array formula:
=INDEX($C3:$AO3, SMALL(IF(ISERROR($C3:$AO3), "", IF($C3:$AO3="", "", MATCH(COLUMN($C3:$AO3), COLUMN($C3:$AO3)))), COLUMN(A1)))
That works perfectly, really appreciate the help!
Hi Oscar,
I've successfully implemented one of your fabulous formulas, but am now stuck on a variation. I am trying to pull names (found in 'PreSeparation SignUp'!C25:C225) of people who have completed at least one section of a course, but not all of it. Incomplete sections are marked "Incomplete" in rows W:BT. Complete sections are left blank.
I think I need to use COUNTBLANK across a dynamic range as part of the selection criteria. I've made a failed attempt inside the second IF statement below. What I want is to count the number of blanks (both real and formula produced) from W to BT (a subset of the larger A:BT range being used in the rest of the formula). If any of these are not blank, I want to import the data from that row. I know the rest of the formula works well, but I just can't quite get the syntax for the COUNTBLANK condition to cooperate.
=IFERROR(
INDEX('PreSeparation SignUp'!$A$25:$BT$225&"",
SMALL(
IF(
FREQUENCY(
IF(('PreSeparation SignUp'!$A$25:$BT$225"")*(COUNTBLANK('PreSeparation SignUp'!$W$25:INDEX('PreSeparation SignUp'!$W$25:$BT$225, MATCH(2,1/'PreSeparation SignUp'!$W$25:$BT$225"")))0,
MATCH(
ROW('PreSeparation SignUp'!$A$25:$BT$225),
ROW('PreSeparation SignUp'!$A$25:$BT$225)), ""),
ROW('PreSeparation SignUp'!$C1)),
COLUMN('PreSeparation SignUp'!$C1)),
"")
Thank you in advance for your help!
Katie
Oops, here's the bit I'm working on:
(COUNTBLANK('PreSeparation SignUp'!$W$25:INDEX('PreSeparation SignUp'!$W$25:$BT$225, MATCH(2,1/('PreSeparation SignUp'!$W$25:$BT$225""))))<50)
Not quite sure what happened, but my first copy/paste just didn't come through correctly at all! Sorry for the multiple messages!
=IFERROR(
INDEX('PreSeparation SignUp'!$A$25:$BT$225&"",
SMALL(
IF(
FREQUENCY(
IF(('PreSeparation SignUp'!$A$25:$BT$225"")*(COUNTBLANK('PreSeparation SignUp'!$W$25:INDEX('PreSeparation SignUp'!$W$25:$BT$225, MATCH(2,1/('PreSeparation SignUp'!$W$25:$BT$225""))))0,
MATCH(
ROW('PreSeparation SignUp'!$A$25:$BT$225),
ROW('PreSeparation SignUp'!$A$25:$BT$225)), ""),
ROW('PreSeparation SignUp'!$C1)),
COLUMN('PreSeparation SignUp'!$C1)),
"")
Katie G,
See attached file:
KatieG.xlsx
Array formula in C11:
My Excel hero! Thank you! This worked beautifully!
Ok, so I'm having trouble developing an inventory spreadsheet using Microsoft Excel 2013. I have all of the inventory scanned in, but i am trying to set up a system that will delete a serial number from sheet 1 (warehouse inventory) when i scan the same serial number on subsequent sheets (2,3,4,5,6etc..) no matter what cell the serial number is in. Sheet 1 is the warehouse inventory and all of the others are inventory on different installation technician's trucks and I need to be able to assign them equipment with ease,as well as have the remaining blank cells in sheet1 be deleted automatically....I can't figure it out!!! Time to turn to smarter people PLEASE help!
I am trying to use the formula =IF(ISERROR(INDEX($X$13:$FN$13, MATCH(0, IF(ISBLANK($X$13:$FN$13), 1, COUNTIF($K$5:K8, $X$13:$FN$13)), 0))),"",INDEX($X$13:$FN$13, MATCH(0, IF(ISBLANK($X$13:$FN$13), 1, COUNTIF($K$5:K8, $X$13:$FN$13)), 0))) but I want to allow repeatition please help
I have been trying to find a formula to remove "blank" cells from list A and put then into a consolidated list B. The contents of list A is text and comes from a formula. the consolidated list B works some of the time, but when I change 1 of the inputs that generates list A list B doesn't work.
List A's formula is:
=INDEX($K$5:$P$250,INT(ROWS(Z$6:Z6)/$K$1),MOD(ROWS(Z$6:Z6)1,$K$1)+1) where K5P250 are the columns of the matrix of text. The matrix can vary in width from 46, which II manually input in cell K1 to = the number of columns in the matrix and the consolidated list starts in cell Z6. All works until I use a 4 column matrix and update cell K1 to a 4 or smaller number..
In Z6 I have tried:
=IFERROR(INDEX(Z$6:Z$1000,SMALL(IF($Z$6:$Z$1000"",ROW($Z$6:$Z$1000)ROW($Z$6)+1),ROWS(AI$6:AI205))),"") Control+Shift+Enter again works great until I update K1 to a 4,3, or a 2 and all of Oscar's variations haven't helped.either.
Thanks for the help in advance.
Doug
Hi Oscar,
Just wanted to thank you for the absolutely incredible stuff you have posted here. At least three times, I have been able to apply your formulas to a project and make it awesome. Just fantastic.
K.
KW,
thank you for your kind words!
In your article "How to extract a unique distinct list from a column in excel" (https://www.getdigitalhelp.com/2009/03/30/howtoextractauniquelistandtheduplicatesinexcelfromonecolumn/) the formula (if you continue to copy it down past the last entry), will leave a "0" in the first cell after (i.e. b17) your last entry (i.e. Almagro, Nicolas in cell B16). Using the "IFERROR" function doesn't solve it (it does for the ensuing cells). Neither, that I can tell, do any of the options on this page. How would you change the formula [=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"")] to get rid of the "0" that will appear in cell B17.
Aaron,
I don´t get a "0". See this picture:
I'm using Excel 2013 in case that matters. See picture below. I get a "0" and then the "#N/A" after that. When I add IFERROR the "#N/A" becomes blank, but the "0" still exists.
https://s17.postimg.org/5flnfck8v/Page_1_Index_Example.jpg
Aaron,
How would you change the formula [=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"")] to get rid of the "0" that will appear in cell B17?
The named range List is larger than necessary. for example A2:A21 or your range contains a blank cell.
Yes, the list is larger than necessary on purpose. You should, theoretically, be able to autofill data using Index and Vlookup. Having a list larger than necessary allows room to accomodate users with different amounts of data. I ended up eliminating the "0" issue using an IF statement. I was just thinking there was another way. I'm using Excel 2013...maybe that is contributing to it... In any event, my fix worked even if it wasn't elegant. I will upload the file I did so you can see what I was doing and what I did to fix it (you don't need to do anything with it, it's just for your reference if your interested). I appreciate your help. Have a good one.