## Excel: Remove blank cells

**Problem:** Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks.

**Answer:**

In this blog post I´ll provide two solutions on how to remove blank cells and a solution on how to remove blank rows:

### 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:

### 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.

### 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)

### Download excel sample file for this tutorial:

remove-blanks_new.xls

(Excel 97-2003 Workbook *.xls)

### Functions used in this tutorial:

**SMALL(**array, k**)** returns the k-th smallest number in this data set.

**ROW(**reference**)**

returns the row number of a reference

**INDEX(**array;row_num;[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

## Remove blank cells (keyboard shortcut F5)

- Select range
- Press F5

- Click "Special"

- Click "Blanks"
- Click OK!
- Right click on one of the selected blank cells and select "Delete.."
- Click "Shift cells up"
- Click OK!

## 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

**Download excel *.xlsx file**

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.

A1)))$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

--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

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 non-volatile 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 http://www.get-digital-help.com/2007/09/16/excel-remove-blank-cells/ 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 Xcelsius-enabled 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 non-blank 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$2-1;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 multi-column 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:

http://www.get-digital-help.com/wp-content/uploads/2007/09/Remove-blanks-from-a-cell-range.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 non-empty 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 "row-by-row" instead "for-all-area" (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 valueCan you explain in greater detail?

The best one:

G14:G19 is the array with blanks and non-blanks. 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:

Remove-blank-rows.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 laugh-it was free!)

I'm, as you can guess, trying to remove blank lines. I haven'ttried allof the formulas on the ->http://www.get-digital-help.com/2007/09/16/excel-remove-blank-cells/ =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:

Remove-blank-rows-from-a-cell-range-formula-excel2003.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:

Remove-blank-rows-from-a-cell-range-formula-excel2003_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 'Pre-Separation Sign-Up'!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('Pre-Separation Sign-Up'!$A$25:$BT$225&"",

SMALL(

IF(

FREQUENCY(

IF(('Pre-Separation Sign-Up'!$A$25:$BT$225"")*(COUNTBLANK('Pre-Separation Sign-Up'!$W$25:INDEX('Pre-Separation Sign-Up'!$W$25:$BT$225, MATCH(2,1/'Pre-Separation Sign-Up'!$W$25:$BT$225"")))0,

MATCH(

ROW('Pre-Separation Sign-Up'!$A$25:$BT$225),

ROW('Pre-Separation Sign-Up'!$A$25:$BT$225)), ""),

ROW('Pre-Separation Sign-Up'!$C1)),

COLUMN('Pre-Separation Sign-Up'!$C1)),

"")

Thank you in advance for your help!

-Katie

Oops, here's the bit I'm working on:

(COUNTBLANK('Pre-Separation Sign-Up'!$W$25:INDEX('Pre-Separation Sign-Up'!$W$25:$BT$225, MATCH(2,1/('Pre-Separation Sign-Up'!$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('Pre-Separation Sign-Up'!$A$25:$BT$225&"",

SMALL(

IF(

FREQUENCY(

IF(('Pre-Separation Sign-Up'!$A$25:$BT$225"")*(COUNTBLANK('Pre-Separation Sign-Up'!$W$25:INDEX('Pre-Separation Sign-Up'!$W$25:$BT$225, MATCH(2,1/('Pre-Separation Sign-Up'!$W$25:$BT$225""))))0,

MATCH(

ROW('Pre-Separation Sign-Up'!$A$25:$BT$225),

ROW('Pre-Separation Sign-Up'!$A$25:$BT$225)), ""),

ROW('Pre-Separation Sign-Up'!$C1)),

COLUMN('Pre-Separation Sign-Up'!$C1)),

"")

Katie G,

See attached file:

Katie-G.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 K5-P250 are the columns of the matrix of text. The matrix can vary in width from 4-6, 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" (http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/) 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.

http://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

Listis 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.

I am trying to make a workbook with two sheets. One sheet will have data including the prices of various items. Second sheet will have formatted invoice which will only receive selected data from the Sheet one. This selection in sheet one will be done by type Y or N in the end of every record. Those with N should NOT be in the invoice sheet and all with Y should be in invoice sheet without any blank records in it.

I have tried a lot your formula but it doesnt work. Struggling a lot to break it in small segments to work. Can you help me in this regard.

Also when I try =IF(LIST"",1,2) it results #Value!

Riz,

My email is [email protected]. If you want, I can send you a file I did that mirrors what you want to do close enough that you should be able to work out a solution for your situation.

Aaron

aaronb123...outlook.com. Fill in the @ at the ...

I have sent you an email Aaron.

I would like to use your

=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)))

formula against an "Excel Table" (ie using Table Nomenclature), in such a way, that as the table grows in number of rows, the resulting array adjusts accordingly.

Can you advise?

Many thanks for your contribution and suppport.

Kind regards.

DMurray3,

see attached file:

DMurray3.xlsx

Many thanks Oscar... Your recommendation works like a charm...

Kind regards.

Oscar,

I am working with this formula to copy a long list of inventory from one tab to another. I want all the blanks to be skipped. This list will be updated 2-3 times a week. I am looking to only do this formula once and as inventory changes in the other tab, this "Master" list can change as well. Any help or solution?

Thank you.