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

Hi

As the formula needs to be an array one + you're pulling info. from several columns I would highly recommend you use a Helper column (can be hidden) to avoid re-calculating the matching rows in your Master table:

Blue table is named TBL_MASTER

Created a named var. NbActiveClients with formula: =COUNTIF(TBL_MASTER[Active:],"yes")

In A11:

=IF(ROWS($1:1) > NbActiveClients, "", AGGREGATE(15,6,1/(TBL_MASTER[Active:]="yes")*(ROW(TBL_MASTER[Active:])-ROW(TBL_MASTER[#Headers])),ROWS($1:1)))

Enter only has AGGREATE is a array function

In B11:

=IF([@HELPER]="","",INDEX(TBL_MASTER[Client Name:],$A11))

and copy right

SAMPLE here