CODEWITHSUNDEEP
excelindexingoffset
Sachin Chhittarka
Sachin Chhittarka

Reputation: 1

Excel Formula to skip blank

I want to create dynamic formula to sort out values from column. Formula to fill in the cell when it contains text and skip the cell if that is blank and go to the next one.

Range is

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

Result from the formula

-A
-E
-F
-H

Upvotes: 0

Views: 4413

Answers (1)

Mrig
Mrig

Reputation: 11727

I am assuming your data is in Column A starting from A1. Enter the following array formula in Cell B1.

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

Note: This is an array formula so you'll have to commit it by pressing Ctrl+Shift+Enter.

See the image below:

enter image description here

Upvotes: 1

Related Questions