Excel Sequence Filtered Tables with Vstack

Question

I am attempting to display a bunch of filtered tables using sequence and vstack but I'm struggling to figure out how I might be able to do this, here is the formula I'm using and I'll provide a sample of my table beneath that:

=VSTACK(FILTER(Table1[[Component]:[Source]],Table1[Assembly]="B"&SEQUENCE(COUNTA(B:B)),""))

Assembly	Component	Quantity	Source
10073716	1130FTB26WM-MS-GRPH	1	PHANTOM
10073716	1530FCY27WM-MS-GRPH	2	PHANTOM
10073716	1B45-GRPH	2	PHANTOM
10073716	GLD01-BK	6	STOCK
10073716	OFS-IDCT-II	1	STOCK
10073716	R0428	3	STOCK
10073716	R0549	2	STOCK
10073716	R0703	1	STOCK
10073716	R0906	1	STOCK
10073716	R0921	2	STOCK
10073716	R0922	1	STOCK
10073716	R1013	2	STOCK
1130FTB26WM-MS-GRPH	1130WM-1515-MS	1	STOCK
1130FTB26WM-MS-GRPH	1BP	4	STOCK
1130FTB26WM-MS-GRPH	1CR90-GRPH	2	STOCK
1130FTB26WM-MS-GRPH	1F12-GRPH	2	STOCK
1130FTB26WM-MS-GRPH	R0363	4	STOCK
1130FTB26WM-MS-GRPH	R0364	4	STOCK
1130FTB26WM-MS-GRPH	R0365	2	STOCK
1530FCY27WM-MS-GRPH	1530WM-1515-MS	1	STOCK
1530FCY27WM-MS-GRPH	1BP	3	STOCK
1530FCY27WM-MS-GRPH	1CR180-GRPH	1	STOCK
1530FCY27WM-MS-GRPH	1F18-GRPH	2	STOCK
1530FCY27WM-MS-GRPH	R0363	2	STOCK
1530FCY27WM-MS-GRPH	R0364	2	STOCK
1530FCY27WM-MS-GRPH	R0365	2	STOCK
1530FCY27WM-MS-GRPH	R0366	1	STOCK
1530FCY27WM-MS-GRPH	R0373	1	STOCK
1B45-GRPH	1B45-BBDAC	1	STOCK
1B45-GRPH	OM-1B45-GRPH	1	STOCK

The formula shown above my sample table is being put in cell D2 (below some headers).

In cell A2 I am entering an assembly item (10073716), then in Column B I'm using a filtered list to display all of the components with a source of PHANTOM based on the assembly item in A2 (so the list in column B is 1130FTB26WM-MS-GRPH, 1530FCY27WM-MS-GRPH, & 1B45-GRPH).

I'm hoping the end result will look like below and be dynamically scalable if there are more or less items listed in column B:

Component	Quantity	Source
1130WM-1515-MS	1	STOCK
1BP	4	STOCK
1CR90-GRPH	2	STOCK
1F12-GRPH	2	STOCK
R0363	4	STOCK
R0364	4	STOCK
R0365	2	STOCK
1530WM-1515-MS	1	STOCK
1BP	3	STOCK
1CR180-GRPH	1	STOCK
1F18-GRPH	2	STOCK
R0363	2	STOCK
R0364	2	STOCK
R0365	2	STOCK
R0366	1	STOCK
R0373	1	STOCK
1B45-BBDAC	1	STOCK
OM-1B45-GRPH	1	STOCK

I'm happy to change my strategy if this isn't possible. Thanks in advance.

Answer 1

=LET(p,FILTER(Table1[Component],(Table1[Assembly]="10073716")*(Table1[Source]="PHANTOM")),
FILTER(Table1[[Component]:[Quantity]],ISNUMBER(XMATCH(Table1[Assembly],p))))

This first declares p: the filtered Table1's [Component] where [Source] equals "PHANTOM".

Then this is used inside another filter: The filter of Table1's columns [Component]:[Quantity] where [Assembly] matches any of the p-assemblies.

In your example you searched for 10073716 typing it in A2, that would make: =LET(p,FILTER(Table1[Component],(Table1[Assembly]=A2)*(Table1[Source]="PHANTOM")),FILTER(Table1[[Component]:[Quantity]],ISNUMBER(XMATCH(Table1[Assembly],p))))

Please note that I can't tell if the [Assembly] are all text, or number. make sure you use the same format when searching, or use: =LET(p,FILTER(Table1[Component],(""&Table1[Assembly]=""&E1)*(Table1[Source]="PHANTOM")),FILTER(Table1[[Component]:[Quantity]],ISNUMBER(XMATCH(Table1[Assembly],p))))