Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper Contributor

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.

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

@M. Ramzan -ul-Mubarak 

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.

@M. Ramzan -ul-Mubarak 

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

@Sergei Baklan 

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.

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

@Subodh_Tiwari_sktneer 

Awesome.

It is also working.

Thanks very very very much.:)

@Sergei Baklan 

Awesome
That is working.
Thanks very very very much.:)
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.

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

Please advise how to?

 

@Subodh_Tiwari_sktneer 

1 best response

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

@M. Ramzan -ul-Mubarak 

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.

View solution in original post