Forum Discussion

Copper Contributor

Aug 28, 2023

Solved

How to get list ignoring blank cells

I am having data in table with 2 column in one sheet. Column 1 has data in text form. Column 2 has data in number form. I need result in another sheet with following condition. - Ignore blank ...

excel

Formulas and Functions

OliverScheurich
Aug 28, 2023
Yashesh_Shah
=IFERROR(INDEX($A$2:$A$25,SMALL(IF($B$2:$B$25<>"",ROW($A$2:$A$25)-1),ROW(A1))),"")
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

Yashesh_Shah

Copper Contributor

Aug 28, 2023

Thanks, it is working in 1st row. How to drag for all rows??
When double clicked on bottom right corner, it is not showing another result.

OliverScheurich

Gold Contributor

Aug 28, 2023

Yashesh_Shah

You are welcome. Double click doesn't work if the formula hasn't already been dragged down. Select the cell with the formula and then drag the fill handle across the cells you want to fill. In the attached file i've dragged the formula down the column.

Here is additional information:

Fill a formula down into adjacent cells - Microsoft Support

get list ingnoring blank cells.xlsx9 KB

PeterBartholomew1
Silver Contributor
Aug 28, 2023
OliverScheurich
Oh well, problem solving is so much more rewarding when one understands the problem! I wasn't sure whether ignoring a blank meant leaving it as it is or removing it!
The subsequent discussion reminded me of the fact that I haven't used relative referencing and fill down for eight years now! Back in the day it meant committing every formula with CSE; that was painful!