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

etaf · May 2, 2023

i have just seen here

Allowing an array (1 or zero) to read from a cell address

I have this formula below which looks at 52 columns and I enter a 1 if I want it to show the column or a 0 if I don't Is there any way instead of manually changing 1's to 0's I can get it to read the 1 or 0 from a cell address I did try to replace a zero with a cell address (say D11) but it did...

www.mrexcel.com

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 numbers of the columns needed

I have used this type of array if i dont want to show all the columns , quite a few times in different spreadsheets in use, with different formulas - but all use
FILTER(FILTER(),{1,1,0,0})

and i cant seem to get to work with this particular array

=SORT(FILTER(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),{1,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,0}),(1))

perhaps it does not apply to this FILTER / FILTER option

or maybe a much better way to do it

Not in a hurry , as i say i noticed a possible better solution

CM test Sheet - bug correction - VERSION 2.1.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

$ 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

$ -

$ 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(FILTER(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),{1,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,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

PSA

PWC CC

$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

$0.00

100.000%

1/31/23

5

name-11

$10,000.00

$2,300.00

$0.00

$7,700.00

23.000%

2/21/23

6

name-12

$10,000.00

$0.00

$12.00

$10,012.00

-0.120%

2/13/23

7

name-13

$10,000.00

$1,000.00

$130.50

$0.00

$9,130.50

8.695%

2/3/23

8

name-14

$10,000.00

$4,000.00

$775.50

$0.00

$6,775.50

32.245%

2/14/23

9

name-15

$10,000.00

$6,000.00

$7,000.00

$0.00

$11,000.00

-10.000%

1/31/23

10

name-2

Closed

$0.00

$12.00

Closed

2/13/23

11

name-3

$10,000.00

$9,000.00

$0.00

$10,000.00

0.000%

2/3/23

12

name-4

$10,000.00

$7,500.00

$775.50

$0.00

$3,275.50

67.245%

2/14/23

Active

Cell Formulas
Range	Formula
O2:O12	O2	=IF(A2="","",SUM(K2)-(L2-M2)+(N2))
P2:P12	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,A21:Q500,A20:J20,Q20	Expression	=$N2>0	text	YES
A2:Q19,A21:Q500,A20:J20,Q20	Expression	=AND($L2<>"",($L2-$M2)<=0)	text	NO
A2:Q19,A21:Q500,A20:J20,Q20	Expression	=AND($P2<>"",$P2<=0.25)	text	NO
A2:Q19,A21:Q500,A20:J20,Q20	Expression	=AND($P2<>"",$P2<=0.5)	text	NO
A2:Q19,A21:Q500,A20:J20,Q20	Expression	=AND($P2<>"",$P2<=0.75)	text	NO

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

Fluff · May 2, 2023

The Choosecols approach works for me like

Fluff.xlsm

E

F

G

H

I

J

K

1

Clients under

35

<- 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

2000

0

20

200

2220

-0.11

4

name-11

10000

2300

0

7700

0.23

5

name-12

10000

0

12

10012

-0.0012

6

name-13

10000

1000

130.5

0

9130.5

0.08695

7

name-14

10000

4000

775.5

0

6775.5

0.32245

8

name-15

10000

6000

7000

0

11000

-0.1

9

name-3

10000

9000

0

10000

0

Dashboard

Cell Formulas
Range	Formula
E3:K9	E3	=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),1,11,12,13,14,15,16),(1))
Dynamic array formulas.

etaf · May 2, 2023

cool, thank you so much and for the quick response
I was applying in the wrong place

Brilliant, i can modify a few formulas now
and even possibly use some sort of indirect to get the columns to view from a cell maybe

Fluff · May 2, 2023

If your headers are an exact match you could use

Fluff.xlsm

E

F

G

H

I

J

K

1

Clients under

35

<- 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 O

Header P

Header N

Header M

3

Flinstone, Fred

2000

0

2220

-0.11

200

20

4

name-11

10000

2300

7700

0.23

0

5

name-12

10000

0

10012

-0.0012

12

0

6

name-13

10000

1000

9130.5

0.08695

0

130.5

7

name-14

10000

4000

6775.5

0.32245

0

775.5

8

name-15

10000

6000

11000

-0.1

0

7000

9

name-3

10000

9000

10000

0

9000

Dashboard

Cell Formulas
Range	Formula
E3:K9	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.

etaf · May 2, 2023

wow - i will have a sit down and coffee and work through that , and see if i can apply to the none sensitised real sheet, looks easy - but then all your solutions look easy - often when you use LET() ,

yes the headers are very often the same and used in a summary or even more spreadsheet

or answering questions here and other forums

Fluff · May 2, 2023

Glad to help & thanks for the feedback.

etaf · May 2, 2023

ok, that worked with the real data - thank you - even with some long text header

any speed advantage ??? to any of the formulas or just like a lot of excel - multiple wys to achieve same result

will make it a lot easier to match headers - no need to count columns or work out 1's and 0's
i end up putting numbers in all the columns

Fluff · May 2, 2023

I've no idea what the speed difference is, but I suspect it will be pretty negligible.

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

etaf

Well-known Member

Allowing an array (1 or zero) to read from a cell address

Excel Facts

Fluff

MrExcel MVP, Moderator

etaf

Well-known Member

Fluff

MrExcel MVP, Moderator

etaf

Well-known Member

Fluff

MrExcel MVP, Moderator

etaf

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

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

Well-known Member

Excel Facts

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock