Function to convert array to Range

[ilcaa]

i have a table with 2500 rows, 20 columns. i use filter(table25, table25[colX]="Y") and it returns the table filtered on Y, but I dont need all the columns so i delete some. I cant do that with an array so I select the range and do a copy, 'paste as values'. But wondering if there is a function i can wrap the filter in so it does the conversion in 1 step.

i tried ArrayToText(Filter(....) ) but i get a !CALC error... but removing the ArrayToText returns the array properly

 

[Peter_SSs]

This sort of thing?

ilcaa.xlsm
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E			A	D	E
2	a	f	k	Y	p			a	Y	p
3	b	g	l	N	q			c	Y	r
4	c	h	m	Y	r			d	Y	s
5	d	i	n	Y	s
6	e	j	o	N	t
7
Sheet1

Cell Formulas
Range	Formula
H2:J4	H2	=FILTER(FILTER(Table1,Table1[D]="Y"),COUNTIF(H1:J1,Table1[#Headers]))
Dynamic array formulas.

 

[RoryA]

Do you have the CHOOSECOLS function available?

 

[ilcaa]

Do you have the CHOOSECOLS function available?

yes i have 365 for Windows... what would that solution look like?

 

[Peter_SSs]

It could be like this.

ilcaa.xlsm
	A	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E			A	D	B
2	a	f	k	Y	p			a	Y	f
3	b	g	l	N	q			c	Y	h
4	c	h	m	Y	r			d	Y	i
5	d	i	n	Y	s
6	e	j	o	N	t
Sheet1

Cell Formulas
Range	Formula
H2:J4	H2	=CHOOSECOLS(FILTER(Table1,Table1[D]="Y"),1,4,2)
Dynamic array formulas.

 

[Peter_SSs]

Also, if you might add/delete/reorder table columns in the original table, you can instead use the headings that you are interested in like this and the results will automatically update in the mentioned circumstances.

ilcaa.xlsm
	A	B	C	D	E	F															U	V	W	X
1	A	B	C	D	E																	A	D	B
2	a	f	k	Y	p																	a	Y	f
3	b	g	l	N	q																	c	Y	h
4	c	h	m	Y	r																	d	Y	i
5	d	i	n	Y	s
6	e	j	o	N	t
7
Sheet1

Cell Formulas
Range	Formula
V2:X4	V2	=CHOOSECOLS(FILTER(Table1,Table1[D]="Y"),MATCH(V1:X1,Table1[#Headers],0))
Dynamic array formulas.