FILTER Function to pull a range - BUT within the filtered range only a part of the contents of 1 column

[etaf]

i have been playing with the FILTER function and help here earlier today

using choosecols() instead of using an array of {1,0,0,1} type within a filter

i have just seen here https://www.mrexcel.com/board/threads/allowing-an-array-1-or-zero-to-read-from-a-cell-address.1236174/#post-6055201 a way to instead of using an array of {1,0,1,0,0,0,0,1,1,1} in a filter to show columns based on a FILTER that a CHOOSECOLS() is used , and just provide the...

www.mrexcel.com

but now i have a new question

i'm using this formula - thanks to @Fluff

=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1))

which is working great

BUT now
rather than show all of the contents of column A
I would like to use some thing like a textbefore ( a2, char(10) )

i have tried this in a helper column and it works - really great with the column header options now , as i can just call the new column Client Name and change in summary - and extend the ranges and it works - PERFECT
i used
IFERROR ( TEXTBEFORE ( A2, char(10) ) ) and copied down - and hid column - so that works - BUT i would like to keep in filter

Can i only show part of 1 column in the filter

in the data sheet i have in cell A2

Flinstone, Fred
123456789
abced ef g

but i only want to pull into the summary sheet , using FILTER - the first line from A2
Flinstone, Fred

Wayne FORUM Help.xlsx
	E	F	G	H	I	J	K
1	Clients under	20%	<- enter the Number - NOT a Percent - EG for 35%, just type in 35 - Trust Balance to Minimum Retainer % required to extract the summary below
2	headerA	HeaderK	headerL	Header M	Header N	Header O	Header P
3	Flinstone, Fred 123456789 abced ef g	$ 2,000.00	$ -	$ 20.00	$ 200.00	$ 2,220.00	-11%
4	name-12	$ 10,000.00	$ -	$ -	$ 12.00	$ 10,012.00	0%
5	name-13	$ 10,000.00	$ 1,000.00	$ 130.50	$ -	$ 9,130.50	9%
6	name-15	$ 10,000.00	$ 6,000.00	$ 7,000.00	$ -	$ 11,000.00	-10%
7	name-3	$ 10,000.00	$ 9,000.00	$ 9,000.00	$ -	$ 10,000.00	0%
8	name-7	$ 10,000.00	$ -	$ -	$ 12.00	$ 10,012.00	0%
Summary

Cell Formulas
Range	Formula
E3:K8	E3	=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Active!_FilterDatabase	=Active!$A$1:$Q$6	E3
Active!Print_Titles	=Active!$1:$1	E3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:K201	Expression	=$E3<>""	text	NO

CM test Sheet - bug correction - VERSION 2.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	HeaderA	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	HeaderK	headerL	Header M	Header N	Header O	Header P	Header16
2	Flinstone, Fred 123456789 abced ef g										$2,000.00	$0.00	$20.00	$200.00	$2,220.00	-11.000%	1/3/23
3	name-1										$10,000.00	$9,000.00	$10.00	$0.00	$1,010.00	89.900%	2/21/23
4	name-10										$10,000.00	$10,000.00	$0.00	$0.00	$0.00	100.000%	1/31/23
5	name-11										$10,000.00	$2,300.00	$0.00	$0.00	$7,700.00	23.000%	2/21/23
Active

Cell Formulas
Range	Formula
O2:O5	O2	=IF(A2="","",SUM(K2)-(L2-M2)+(N2))
P2:P5	P2	=IFERROR(IF(K2="Closed","Closed",((L2-M2-N2)/K2)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:O19,L21:O500	Expression	=$K2="closed"	text	NO
A2:Q19,A21:Q500,A20:J20,Q20	Expression	=$P2="closed"	text	YES
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=$N2>0	text	YES
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($L2<>"",($L2-$M2)<=0)	text	NO
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($P2<>"",$P2<=0.25)	text	NO
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($P2<>"",$P2<=0.5)	text	NO
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($P2<>"",$P2<=0.75)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C5	List	=Reference!$A$2:$A$13

 

[etaf]

bump - added a drop box line to the above , if its easier

I could use the formula =IFERROR(TEXTBEFORE(Active!A2,CHAR(10)),Active!A2) - as shown now in column D - summary -
in a helper column on the active sheet and pull over the ist line of headerA - column A, with a new header - headerA-Name

but would like to do without the helper column if possible in the following formula

=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1))

Wayne FORUM Help.xlsx
	D	E	F	G	H	I	J	K
1		Clients under	100%
2	Name I want	headerA	HeaderK	headerL	Header M	Header N	Header O	Header P
3	Flinstone, Fred	Flinstone, Fred 123456789 abced ef g	$ 2,000.00	$ -	$ 20.00	$ 200.00	$ 2,220.00	-11%
4	name-1	name-1 -------2nd line	$ 10,000.00	$ 9,000.00	$ 10.00	$ -	$ 1,010.00	90%
5	name-10	name-10 ------ line 2	$ 10,000.00	$ 10,000.00	$ -	$ -	$ -	100%
6	name-11	name-11 ------- line 2 ---------line 3	$ 10,000.00	$ 2,300.00	$ -	$ -	$ 7,700.00	23%
7	name-12	name-12	$ 10,000.00	$ -	$ -	$ 12.00	$ 10,012.00	0%
Summary

Cell Formulas
Range	Formula
E3:K7	E3	=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1))
D3:D7	D3	=IFERROR(TEXTBEFORE(Active!A2,CHAR(10)),Active!A2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Active!_FilterDatabase	=Active!$A$1:$Q$6	E3
Active!Print_Titles	=Active!$1:$1	E3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:K201	Expression	=$E3<>""	text	NO

I could use the formula =IFERROR(TEXTBEFORE(Active!A2,CHAR(10)),Active!A2)
in a helper column on the active sheet and pull over the ist line of headerA - column A
but would like to do without the helper column if possible

Wayne FORUM Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	HeaderA	Header1	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	HeaderK	headerL	Header M	Header N	Header O	Header P	Header16
2	Flinstone, Fred 123456789 abced ef g										$2,000.00	$0.00	$20.00	$200.00	$2,220.00	-11.000%	1/3/23
3	name-1 -------2nd line										$10,000.00	$9,000.00	$10.00	$0.00	$1,010.00	89.900%	2/21/23
4	name-10 ------ line 2										$10,000.00	$10,000.00	$0.00	$0.00	$0.00	100.000%	1/31/23
5	name-11 ------- line 2 ---------line 3										$10,000.00	$2,300.00	$0.00	$0.00	$7,700.00	23.000%	2/21/23
6	name-12										$10,000.00	$0.00	$0.00	$12.00	$10,012.00	-0.120%	2/13/23
Active

Cell Formulas
Range	Formula
O2:O6	O2	=IF(A2="","",SUM(K2)-(L2-M2)+(N2))
P2:P6	P2	=IFERROR(IF(K2="Closed","Closed",((L2-M2-N2)/K2)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:O19,L21:O500	Expression	=$K2="closed"	text	NO
A2:Q19,A21:Q500,A20:J20,Q20	Expression	=$P2="closed"	text	YES
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=$N2>0	text	YES
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($L2<>"",($L2-$M2)<=0)	text	NO
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($P2<>"",$P2<=0.25)	text	NO
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($P2<>"",$P2<=0.5)	text	NO
A2:Q19,A20:J20,Q20,A21:Q500	Expression	=AND($P2<>"",$P2<=0.75)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C6	List	=Reference!$A$2:$A$13

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Alex Blakenburg]

I am sure someone can come up with a shorter version but this should work.
• The TextBefore was erroring out on your last example that didn't have an Lf at the end so I added a trailing Lf to all lines

=LET(fltr,SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1)),
         HSTACK(TEXTBEFORE(CHOOSECOLS(fltr,1)&CHAR(10),CHAR(10)),
         DROP(fltr,,1)))

 

[etaf]

thanks so much for that - worked in the real data as well, cool

i see how you added a Char(10) to the Hstack, so it will not error - if not found - i must play more with the LET() function
and also work on the hstack -

But thanks - for that

 

[Alex Blakenburg]

My pleasure. Glad I could help.
I am used to seeing you on the other side of the fence answering questions

 

[etaf]

I am used to seeing you on the other side of the fence answering questions

yep, bit of a hobby , keeping the brian active - now retired. i used to moderate/admin a few IT forums, helping people with computer issues - but the problem, i was not keeping up practically with issues, so I do very little now on that side , and needed something else

thanks