SOLVED

# Formula Required to Extract data from Column A based on Non -zero values in Column B

Copper Contributor

# 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.

8 Replies
best response confirmed by M. Ramzan -ul-Mubarak (Copper Contributor)
Solution

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

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.

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

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

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

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.

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

Awesome.
That is working.
Thanks very very very much.:)

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

Awesome.

It is also working.

Thanks very very very much.:)

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

Awesome
That is working.
Thanks very very very much.:)

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

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.

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

Is there anyway to display both columns (A,B) of the fields ?

1 best response

Accepted Solutions
best response confirmed by M. Ramzan -ul-Mubarak (Copper Contributor)
Solution

# Re: Formula Required to Extract data from Column A based on Non -zero values in Column B

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.