Dynamic spill filtered based on other spill

[Power Hely]

Hello:

I am trying to help my sales department come up with a dynamic sheet for rep group presentations. All data is stored in one Master Data table.

I have Column A working as =SORT(UNIQUE(FILTER(MasterData[Account Name],MasterData[Rep]=SelectRep))). SelectRep is a cell where they can select the desired value.
Column B through however many are needed are based on = TRANSPOSE(FILTER(MasterData[Product],MasterData[Account Name]=A4)), where A4 is the first row in the spill Column A dynamic array. But they have to manually fill down the sheet based on however many values are spilled from the first dynamic array. Can I modify the second dynamic array function based on the first dynamic array function, so they both spill down and over?

Thank you in advance for any assistance you could provide.

Best,

Power Hely

[#2]

@Power Hely 

Yes, you can modify the second dynamic array function based on the first dynamic array function so that they both spill down and over.

You can use the new dynamic array formulas feature in Excel to achieve this.

 When you press Enter to confirm your formula, Excel will dynamically size the output range for you, and place the results into each cell within that range.

Here’s an example of how you can modify your second dynamic array function based on your first dynamic array function:

=TRANSPOSE(FILTER(MasterData[Product],(MasterData[AccountName]=A4)*(MasterData[Rep]=SelectRep)))

You can replace A4 with a reference to the first cell in your spill Column A dynamic array. This will make sure that your second dynamic array spills down and over based on your first dynamic array.

I hope this helps!

[#3]

@Power Hely 

There is a catch.  The products associated with each account form a list, and so the table you are trying to create is a nested array.  Because of limitations build into Excel to retain backward compatibility, this is not properly supported.  The workaround is to use REDUCE and built the array step by step using VSTACK.

=LET(
    account,  UNIQUE(FILTER(MasterData[Account Name], MasterData[Rep] = SelectRep)),
    products, REDUCE("", account,
        LAMBDA(list, ac,
            VSTACK(list, TRANSPOSE(FILTER(MasterData[Product], MasterData[Account Name] = ac)))
        )
    ),
    HSTACK(account, IFNA(DROP(products, 1), ""))
)

It is possible to make the worksheet formula more readable by using a further Lambda function to return the lists of products using

= LET(
    account,  UNIQUE(FILTER(MasterData[Account Name], MasterData[Rep] = SelectRep)),
    products, MultipleFilterλ(MasterData[Product], MasterData[Account Name], account),
    HSTACK(account, IFNA(DROP(products, 1), ""))
 )

but the simplification is achieved at the expense of having to define a further Lambda function MultipleFilterλ

= LAMBDA(products, accounts, selectedACs,
    REDUCE("", selectedACs, 
      LAMBDA(list, ac, VSTACK(list, TRANSPOSE(FILTER(products, accounts = ac)))
    )
  )

[#4]

@Peter Bartholomew this is incredible! And reduces me to one spill formula instead of two. I'll need to do some reading to understand it, as VSTACK, HSTACK, REDUCE, and IFNA are new to me, but it works perfectly. I greatly appreciate you, both for solving my problem and helping me learn more about how to create better dynamic array functions!