SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1038123%22%20slang%3D%22en-US%22%3EFormula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038123%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys.%3CBR%20%2F%3EI%20need%20to%20know%20the%20formula%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B.%3CBR%20%2F%3EI%20have%20successfully%20done%20this%20task%20in%20past%20to%20extract%20the%20data%20from%20same%20column%20using%20the%20combination%20of%20different%20excel%20functions%20e.g.%20Index%2C%20Aggregate%2C%20Row%2C%20CountA%20etc.%3CBR%20%2F%3EBut%20unable%20to%20do%20it%20when%20criteria%20is%20in%20another%20column.%3CBR%20%2F%3EPlease%20look%20the%20attachment.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1038123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1038197%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F97737%22%20target%3D%22_blank%22%3E%40M.%20Ramzan%20-ul-Mubarak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20E2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(ROWS(D%242%3AD2)%26gt%3BCOUNTIFS(%24A%242%3A%24A%2410003%2C%22%26lt%3B%26gt%3B%22%2C%24B%242%3A%24B%2410003%2C%22%26gt%3B0%22)%2C%22%22%2CINDEX(%24A%242%3A%24A%2410003%2CAGGREGATE(15%2C6%2C(ROW(%24A%242%3A%24A%2410003)-ROW(%24A%242)%2B1)%2F((%24A%242%3A%24A%2410003%26lt%3B%26gt%3B%22%22)*(%24B%242%3A%24B%2410003%26gt%3B0))%2CROWS(D%242%3AD2))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20copy%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1038880%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F97737%22%20target%3D%22_blank%22%3E%40M.%20Ramzan%20-ul-Mubarak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24A%242%3A%24A%2410003%2CAGGREGATE(15%2C6%2C1%2F(%24B%242%3A%24B%2410003%26lt%3B%26gt%3B0)*(ROW(%24B%242%3A%24B%2410003)-ROW(%24B%241))%2C(ROW()-ROW(%24E%241))))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20E2%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039101%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20(%24A%242%3A%24A%2410003%26lt%3B%26gt%3B%22%22)%20condition%20is%20skipped%2C%20the%20formula%20will%20also%20pick%20all%20the%20blank%20cells%20in%20column%20A%20where%20corresponding%20values%20in%20column%20B%20are%20greater%20than%200%20and%20as%20a%20result%2C%20the%20formula%20cells%20will%20display%20zeros.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039142%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039142%22%20slang%3D%22en-US%22%3E%3CP%3EAwesome.%3CBR%20%2F%3EThat%20is%20working.%3CBR%20%2F%3EThanks%20very%20very%20very%20much.%3A)%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039143%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039143%22%20slang%3D%22en-US%22%3E%3CP%3EAwesome.%3C%2FP%3E%3CP%3EIt%20is%20also%20working.%3C%2FP%3E%3CP%3EThanks%20very%20very%20very%20much.%3A)%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039144%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039144%22%20slang%3D%22en-US%22%3EAwesome%3CBR%20%2F%3EThat%20is%20working.%3CBR%20%2F%3EThanks%20very%20very%20very%20much.%3A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039145%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Required%20to%20Extract%20data%20from%20Column%20A%20based%20on%20Non%20-zero%20values%20in%20Column%20B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039145%22%20slang%3D%22en-US%22%3EYes%20you%20are%20right.%3CBR%20%2F%3EBut%20no%20problem%20with%20me%20because%20in%20my%20data%20set%20there%20is%20no%20such%20record%20in%20which%20Column%20B%20Has%20data%20and%20Column%20A%20has%20no%20data.%3CBR%20%2F%3EI%20given%20that%20one%20line%20in%20sample%20file%20just%20for%20experiment%20%26amp%3B%20better%20solution.%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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