Excel - Array Formula to skip empty cells

问题:

I have an array formula to fill in the cell when it contains text but I want it to ignore the cell if its blank and go to the next one. The issue I am having is that it does not do it in the order i have for example:

Range is

-A
-(BLANK CELL)
-(BLANK CELL)
-D
-E
-F
-(BLANK CELL)
-H
-(BLANK CELL)

Result from the formula

-A
-E
-F
-H

It is skipping the cells that contain letters. CAN SOMEONE HELP??

FORMULA:

{=IF(ISERROR(SMALL(IF(B4:$B$12<>"",ROW(B4:$B$12)),ROW(B4:$B$12)-3)),"",INDEX(B4:$B$12,MATCH(SMALL(IF(B4:$B$12<>"",ROW(B4:$B$12)),ROW(B4:$B$12)-3),IF(B4:$B$12<>"",ROW(B4:$B$12)),0)))}

回答1:

I think the problem is that you have the range as B4:$B$12, so because there are no $ signs around B4 that reference changes as you drag the formula down the column.......but the formula looks a little more complex than you need so I'd rather suggest a better alternative:

Which version of Excel are you using?

In Excel 2007 or later try this version

=IFERROR(INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>"",ROW(B$4:B$12)-ROW(B$4)+1),ROWS(D$4:D4))),"")

Assumes your first cell is D4 (change ROWS(D$4:D4) part to match your actual start cell) and you are dragging the formula down the column

confirmed with CTRL+SHIFT+ENTER

In earlier versions where IFERROR function is not available, you can use this version

=IF(ROWS(D$4:D4)>COUNTA(B$4:B$12),"",INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>"",ROW(B$4:B$12)-ROW(B$4)+1),ROWS(D$4:D4))))

回答2:

Found a simpler and easier way, basically Excel considers all blank cells ("") equal, so another easy way is to select the column that contains the data and click on Remove Duplicates under the Data tab, this will remove (skip) all of the blank empty cells.