Feed results of a spilled array into another spilled array.

[MartyCollins]

Hi all,
So i have a table that I want to filter, thats fine.(formula in H9)

But i want the filtered results to feed into an adjacent formula spilling to the right (I9) which i also want to be dynamic but running into difficulty.
I can get it to work easily as per below non dynamic,but getting it dynamic has me in a spin - ive tried ByRow to feed it into filter, ive obviously tried simply replacing the =H9 with = H9# but getting nowhere there either..


Below is a basic idea of what im trying to achieve.
User selects a car... Optional extras are listed... and then the adjacent formula spills to the right listing all cars with same optional Extras eg Alloys;Aircon etc etc.

Formula I want to be dynamic...

=TRANSPOSE(
FILTER(TblData[Model],
TblData[Optional Extras]=H9))

Thanks,
Martin.

 

[Fluff]

Can you post your sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MartyCollins]

Thanks Fluff- yes i have managed now to get that tool working-- V handy!!
So here it is again.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2								Formula in H9	Formula in I9 which I would like to be dynamic
3								=FILTER( TblData[Optional Extras], TblData[Model]=H6)	=TRANSPOSE( FILTER(TblData[Model], TblData[Optional Extras]=H9))
4
5								Select Model
6								Mondeo
7					TblData
8								Optional Extras	All Models with same extras
9				Make	Model	Optional Extras		Alloys	Mondeo	Camry	Fabia
10				Ford	Mondeo	Alloys		Aircon	Mondeo	Focus
11				Ford	Mondeo	Aircon		Satnav	Mondeo	Puma	Prius
12				Ford	Mondeo	Satnav		Heated Seats	Mondeo	Puma	Laguna	Megane
13				Ford	Mondeo	Heated Seats
14				Ford	Focus	Aircon
15				Ford	Focus	Handsfee entry
16				Ford	Focus	Bluetooth
17				Ford	Puma	Heated Seats
18				Ford	Puma	Satnav
19				Toyota	Camry	Alloys
20				Toyota	Camry	Towbar
21				Toyota	Camry	Automatic Boot
22				Toyota	Camry	Metallic Paint
23				Toyota	Camry	Retracting Aerial
24				Toyota	Prius	Home Charger
25				Toyota	Prius	Satnav
26				Renault	Laguna	Heated Seats
27				Renault	Megane	Towbar
28				Renault	Megane	Heated Seats
29				Skoda	Fabia	Alloys
30				Skoda	Fabia	Heated Windscreen
31
Sheet1

Cell Formulas
Range	Formula
H3:I3	H3	=FORMULATEXT(H9)
H9:H12	H9	=FILTER( TblData[Optional Extras], TblData[Model]=H6)
I9:K9	I9	=TRANSPOSE( FILTER(TblData[Model], TblData[Optional Extras]=H9))
I10:J10,I12:L12,I11:K11	I10	=TRANSPOSE(FILTER(TblData[Model],TblData[Optional Extras]=H10))
Dynamic array formulas.

 

[Fluff]

Thanks for that how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5								Select Model
6								Mondeo
7					TblData
8								Optional Extras	All Models with same extras
9				Make	Model	Optional Extras		Alloys	Mondeo	Camry	Fabia
10				Ford	Mondeo	Alloys		Aircon	Mondeo	Focus
11				Ford	Mondeo	Aircon		Satnav	Mondeo	Puma	Prius
12				Ford	Mondeo	Satnav		Heated Seats	Mondeo	Puma	Laguna	Megane
13				Ford	Mondeo	Heated Seats
14				Ford	Focus	Aircon
15				Ford	Focus	Handsfee entry
16				Ford	Focus	Bluetooth
17				Ford	Puma	Heated Seats
18				Ford	Puma	Satnav
19				Toyota	Camry	Alloys
20				Toyota	Camry	Towbar
21				Toyota	Camry	Automatic Boot
22				Toyota	Camry	Metallic Paint
23				Toyota	Camry	Retracting Aerial
24				Toyota	Prius	Home Charger
25				Toyota	Prius	Satnav
26				Renault	Laguna	Heated Seats
27				Renault	Megane	Towbar
28				Renault	Megane	Heated Seats
29				Skoda	Fabia	Alloys
30				Skoda	Fabia	Heated Windscreen
Report

Cell Formulas
Range	Formula
H9:H12	H9	=FILTER( TblData[Optional Extras], TblData[Model]=H6)
I9:L12	I9	=LET(a,MAX(COUNTIFS(TblData[Optional Extras],H9#)),b,BYROW(H9#,LAMBDA(br,TEXTJOIN("|",,FILTER(TblData[Model],TblData[Optional Extras]=br)))),TRIM(MID(SUBSTITUTE(b,"|",REPT(" ",100)),SEQUENCE(,a,,100),100)))
Dynamic array formulas.

 

[MartyCollins]

Wow -- thanks a Lot!!
I had come across a similar problem and the solution had textjoin in there to "Square the array" (thats proabably a poor explanation!)
Im going to have to take some time to study that formula!!
Thanks again,
Martin.

 

[Fluff]

Glad to help & thanks for the feedback.