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

etaf

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
8,795
Office Version
  1. 365
Platform
  1. MacOS
i have been playing with the FILTER function and help here earlier today

www.mrexcel.com

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 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
EFGHIJK
1Clients under20% <- enter the Number - NOT a Percent - EG for 35%, just type in 35 - Trust Balance to Minimum Retainer % required to extract the summary below
2headerA HeaderK headerL Header M Header N Header O Header P
3Flinstone, Fred 123456789 abced ef g$ 2,000.00$ -$ 20.00$ 200.00$ 2,220.00-11%
4name-12$ 10,000.00$ -$ -$ 12.00$ 10,012.000%
5name-13$ 10,000.00$ 1,000.00$ 130.50$ -$ 9,130.509%
6name-15$ 10,000.00$ 6,000.00$ 7,000.00$ -$ 11,000.00-10%
7name-3$ 10,000.00$ 9,000.00$ 9,000.00$ -$ 10,000.000%
8name-7$ 10,000.00$ -$ -$ 12.00$ 10,012.000%
Summary
Cell Formulas
RangeFormula
E3:K8E3=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
NameRefers ToCells
Active!_FilterDatabase=Active!$A$1:$Q$6E3
Active!Print_Titles=Active!$1:$1E3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:K201Expression=$E3<>""textNO




CM test Sheet - bug correction - VERSION 2.1.xlsx
ABCDEFGHIJKLMNOPQ
1HeaderAHeader1Header2Header3Header4Header5Header6Header7Header8Header9HeaderKheaderLHeader MHeader NHeader OHeader PHeader16
2Flinstone, Fred 123456789 abced ef g$2,000.00$0.00$20.00$200.00$2,220.00-11.000%1/3/23
3name-1$10,000.00$9,000.00$10.00$0.00$1,010.0089.900%2/21/23
4name-10$10,000.00$10,000.00$0.00$0.00$0.00100.000%1/31/23
5name-11$10,000.00$2,300.00$0.00$0.00$7,700.0023.000%2/21/23
Active
Cell Formulas
RangeFormula
O2:O5O2=IF(A2="","",SUM(K2)-(L2-M2)+(N2))
P2:P5P2=IFERROR(IF(K2="Closed","Closed",((L2-M2-N2)/K2)),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2:O19,L21:O500Expression=$K2="closed"textNO
A2:Q19,A21:Q500,A20:J20,Q20Expression=$P2="closed"textYES
A2:Q19,A20:J20,Q20,A21:Q500Expression=$N2>0textYES
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($L2<>"",($L2-$M2)<=0)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.25)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.5)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.75)textNO
Cells with Data Validation
CellAllowCriteria
C2:C5List=Reference!$A$2:$A$13
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
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
DEFGHIJK
1Clients under100%
2Name I want headerA HeaderK headerL Header M Header N Header O Header P
3Flinstone, FredFlinstone, Fred 123456789 abced ef g$ 2,000.00$ -$ 20.00$ 200.00$ 2,220.00-11%
4name-1name-1 -------2nd line$ 10,000.00$ 9,000.00$ 10.00$ -$ 1,010.0090%
5name-10name-10 ------ line 2$ 10,000.00$ 10,000.00$ -$ -$ -100%
6name-11name-11 ------- line 2 ---------line 3$ 10,000.00$ 2,300.00$ -$ -$ 7,700.0023%
7name-12name-12$ 10,000.00$ -$ -$ 12.00$ 10,012.000%
Summary
Cell Formulas
RangeFormula
E3:K7E3=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:D7D3=IFERROR(TEXTBEFORE(Active!A2,CHAR(10)),Active!A2)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Active!_FilterDatabase=Active!$A$1:$Q$6E3
Active!Print_Titles=Active!$1:$1E3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E3:K201Expression=$E3<>""textNO


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
ABCDEFGHIJKLMNOPQ
1HeaderAHeader1Header2Header3Header4Header5Header6Header7Header8Header9HeaderKheaderLHeader MHeader NHeader OHeader PHeader16
2Flinstone, Fred 123456789 abced ef g$2,000.00$0.00$20.00$200.00$2,220.00-11.000%1/3/23
3name-1 -------2nd line$10,000.00$9,000.00$10.00$0.00$1,010.0089.900%2/21/23
4name-10 ------ line 2$10,000.00$10,000.00$0.00$0.00$0.00100.000%1/31/23
5name-11 ------- line 2 ---------line 3$10,000.00$2,300.00$0.00$0.00$7,700.0023.000%2/21/23
6name-12$10,000.00$0.00$0.00$12.00$10,012.00-0.120%2/13/23
Active
Cell Formulas
RangeFormula
O2:O6O2=IF(A2="","",SUM(K2)-(L2-M2)+(N2))
P2:P6P2=IFERROR(IF(K2="Closed","Closed",((L2-M2-N2)/K2)),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L2:O19,L21:O500Expression=$K2="closed"textNO
A2:Q19,A21:Q500,A20:J20,Q20Expression=$P2="closed"textYES
A2:Q19,A20:J20,Q20,A21:Q500Expression=$N2>0textYES
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($L2<>"",($L2-$M2)<=0)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.25)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.5)textNO
A2:Q19,A20:J20,Q20,A21:Q500Expression=AND($P2<>"",$P2<=0.75)textNO
Cells with Data Validation
CellAllowCriteria
C2:C6List=Reference!$A$2:$A$13



Dropbox

www.dropbox.com www.dropbox.com
 
Upvote 0
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

Excel Formula: 
=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)))
 
Upvote 0
Solution
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
 
Upvote 0
My pleasure. Glad I could help.
I am used to seeing you on the other side of the fence answering questions ;)
 
Upvote 0
I am used to seeing you on the other side of the fence answering questions
Click to expand...
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

etaf
  • Solved
using choosecols() instead of using an array of {1,0,0,1} type within a filter
Replies
7
Views
505
Fluff
Fluff
etaf
  • Solved
SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365
Replies
9
Views
810
Georgiboy
Georgiboy
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
127
Fluff
Fluff
A
  • Solved
Help Changing Cell Color to Based of Date referring to a specific Interior Cell Color
Replies
8
Views
528
SanjayGMusafir
SanjayGMusafir
J
  • Solved
trending value projections without counting sundays
Replies
2
Views
298
KRice
K

Forum statistics

Threads
1,224,881
Messages
6,181,540
Members
453,054
Latest member
ezzat

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top