Forum Discussion
Solved
Formula Required to Extract data from Column A based on Non -zero values in Column B
Hey guys. I need to know the formula to Extract data from Column A based on Non -zero values in Column B. I have successfully done this task in past to extract the data from same column using the c...
Please try this...
In E2
=IF(ROWS(D$2:D2)>COUNTIFS($A$2:$A$10003,"<>",$B$2:$B$10003,">0"),"",INDEX($A$2:$A$10003,AGGREGATE(15,6,(ROW($A$2:$A$10003)-ROW($A$2)+1)/(($A$2:$A$10003<>"")*($B$2:$B$10003>0)),ROWS(D$2:D2))))and copy it down.
Another variant is
=IFERROR(INDEX($A$2:$A$10003,AGGREGATE(15,6,1/($B$2:$B$10003<>0)*(ROW($B$2:$B$10003)-ROW($B$1)),(ROW()-ROW($E$1)))),"")in E2
If ($A$2:$A$10003<>"") condition is skipped, the formula will also pick all the blank cells in column A where corresponding values in column B are greater than 0 and as a result, the formula cells will display zeros.
- Yes you are right.
But no problem with me because in my data set there is no such record in which Column B Has data and Column A has no data.
I given that one line in sample file just for experiment & better solution.
