Share via

Creating a dynamic sub list from a master list

Anonymous
2018-07-04T08:22:44+00:00

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!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-07-04T09:07:31+00:00

    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

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2018-07-04T08:42:03+00:00

    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.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2018-07-04T08:59:38+00:00

    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

    0 comments
  4. Anonymous
    2018-07-04T08:57:18+00:00

    Hi,

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

    0 comments
  5. Anonymous
    2018-07-04T08:34:16+00:00

    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

    0 comments