Creating a dynamic sub list from a master list

Question

Hello,

I am struggling to create a sub list from a master list.  I am attempting to create an array formula to address my need but am running into issues being able to pull an entire row of data from a given match within the master list.

From a master list of clients, I am attempting to create a sub list that contains only active clients.  Active or inactive is a variable listed within the master list:

Master List Example:

Client Name:	Client ID:	Res Type:	SIS Tier:	Start Date:	End Date:	DOB:	Active:
Client 1	G646282	PCA	6	01/03/2014		09/17/1984	Yes
Client 2	D236535	PCA	6	06/18/2016		07/19/1959	Yes
Client 3	P029373	HHP	6	03/19/2013	06/01/2018	08/03/1994	No
Client 4	A839939	HHP	6	09/18/2017		06/26/1981	Yes
Client 5	V647839	PCA	5	04/14/2016	05/04/2018	07/06/1999	No

Sub List Need Example:

Client Name:	Client ID:	Res Type:	SIS Tier:	Start Date:	End Date:	DOB:	Active:
Client 1	G646282	PCA	6	01/03/2014		09/17/1984	Yes
Client 2	D236535	PCA	6	06/18/2016		07/19/1959	Yes
Client 4	A839939	HHP	6	09/18/2017		06/26/1981	Yes

I have been stuck on this for weeks any help or a nudge in the right direction is greatly appreciated.  Thank you very much!

Answer 1

Dear Friend,

i would recommend you use the PivotTable or the PowerQuery for your issue, that is the easiest and the simple solution for your issue

Incase of PivotTable - Select the active status and check only "Yes"

Incase of PowerQuery - Convert your data to table range and Select the active status and check only "Yes" - Select Close and Load to - new sheet. as you make charges it will reflect on the new sheet 

Thanks, Faraz Shaikh - fshaikh Excel Master 

https://www.facebook.com/fshaikhExcelMaster/

Subscribe to my YouTube channel for latest updates - CLICK HERE

Answer 2

Thank you for the prompt follow up.  Unfortunately, those two options won't meet with my needs.  I am really looking for a formula solution here.  I am running Excel on an OSX system, so I don't believe that PowerQuery is an option.  As for Pivot table, I really need an option that updates as a I add to the master list.  Thank you again for your thoughts in this regard.

Answer 3

Hi,

I suggest you to duplicate all rows with formula and then filter active clients.

Answer 4

Dear Friend,

please find below the solution for the PivotTable and follow the steps as below 

recommend you to change the data to table range

Add all the fields to row area & active field to filter area

select report layout 

Remove all the subtotals 

Thanks, Faraz Shaikh - fshaikh Excel Master 

https://www.facebook.com/fshaikhExcelMaster/

Subscribe to my YouTube channel for latest updates - CLICK HERE

Answer 5

Enter below formula as an array formula (Ctrl+Shift+Enter) in cell A11 and copy down and to the right (except column F):

=IFERROR(INDEX(A$2:A$8,SMALL(IF($H$2:$H$8="Yes",ROW(A$2:A$8)-ROW(A$2)+1),ROW(1:1))),"")

Regards,

Amit Tandon

www.globaliconnect.com