How to filter data based on condition using non array formula

Question

I have following sample data and want to auto filter them based on status condition Complete using formula only. I know how to filter using array formula and VBA custom function. Right now I am filtering it using following array formula. Due to some limitation, I want to ignore VBA and array formula. Is there any function combination to achieve it as non-array formula?

{=IFERROR(INDEX($B$4:$E$13,SMALL(IF($E$4:$E$13="Complete",ROW($E$4:$E$13)-ROW($E$3)),ROW(1:1)),COLUMN(A$1)),"")}

============================= Sample Data =================================

   ID   User    Task                 Status
----------------------------------------------
Emp-001 Harun   Prepare Document    Complete
Emp-002 Rahim   Print               Complete
Emp-003 Karim   Binding             Progress
Emp-004 Jadhu   Packaging           Complete
Emp-005 Madhu   Prepare Document    Hold
Emp-006 Rahima  Print               Progress
Emp-007 Shila   Binding             Hold
Emp-008 Kaniz   Packaging           Complete
Emp-009 Jamila  Prepare Document    Progress
Emp-010 Rina    Print               Complete

Screenshot:

Any help is greatly appreciated.

You can achieve your goal if you are willing to use a "helper" column in column K — Gary's Student
– Gary's Student, Commented Apr 22, 2018 at 15:45
@pnuts I used Advanced Filter and can achieve it but Advance Filter needs manually triggering. If I can use formulas only then it will filter automatically when I put new data or change status when an user's task is complete. — Harun24hr
– Harun24hr, Commented Apr 22, 2018 at 16:16

Harun24hr · Accepted Answer · 2018-04-22 16:10:54Z

1

Use the following formula in G3 cell then drag and drop to down and right as needed. Hope this will help you.

=IFERROR(INDEX($B$3:$E$12,SMALL(INDEX(ROW($A$1:$A$10)-($E$3:$E$12<>"Complete")*(1E+99)*-1,, ),ROW(1:1)),COLUMN(A$1)),"")

Snapshot:

edited Apr 22, 2018 at 16:10

answered Apr 22, 2018 at 16:04

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harun24hr Over a year ago

Wow! the formula works like magic. It helps me a lot. Thank you so much.

Harun24hr Over a year ago

Basically I get the idea from Jeeps answer to one of my question. Link of that question here.

Gary's Student · Accepted Answer · 2018-04-22 16:13:18Z

0

In K4 enter:

=MATCH("Complete",E:E,0)

In K5 enter:

=IFERROR(MATCH("complete",INDEX(E:E,K4+1):INDEX(E:E,9999),0)+K4,"")

and copy downward. Column K defines the rows of interest.

In G4 enter:

=IFERROR(INDEX(B:B,$K4),"")

copy this both across and downward:

answered Apr 22, 2018 at 16:13

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

2 Comments

Harun24hr Over a year ago

It also works but better solution is without helper column. Thank for your answer.

Gary's Student Over a year ago

@RainyDay I agree completely! I will use harun's method in the future!

Collectives™ on Stack Overflow

How to filter data based on condition using non array formula

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related