Forum Discussion
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 combination of different excel functions e.g. Index, Aggregate, Row, CountA etc.
But unable to do it when criteria is in another column.
Please look the attachment.
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.
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.
Is there anyway to display both columns (A,B) of the fields ?
Please advise how to?
- Awesome
That is working.
Thanks very very very much.:) Awesome.
That is working.
Thanks very very very much.:)
