Forum Discussion
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 in column 2 and Get list of data from column 1. (Simply not required data if column 2 is blank)
Don't want to use filter or sort option or power query option. Data must be prepared by using formula only.
Please suggest.
=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.
10 Replies
=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.
- 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.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:
- Hi Yashesh,
Do you want all the applicable data from the source sheet or only based on some condition using lookup formulas?nimesht To all applicable data
