replace filter

ajm · Thursday at 5:42 PM

i have inherited a report that uses FILTER to transform the raw data input into a meaningful report. The original report used set ranges for each filter formula and these had to be expanded each time new data was added. to get around this, i changed all the ranges to dynamic named ranges (using indirect). the file is ballooning in size each month as its updated. Before FILTER, i would have run formulas adjacent to a pivot table (getting unique data from a list of multiples). This would probably still result in a large file but would it be as large?? any ideas on better method folks?

coulombevin · Friday at 9:40 PM

Hi ajm,

Based on my experience, filters and formulas end-up eating your memory in large file. If you use Excel as à huge database, I would recommand thinking about vba. We could take a look if you have some example or dummy data if there is something to do before going towards vba.

Bests regards,

Vincent

Alex Blakenburg · Friday at 9:58 PM

Have you looked at Power Query ? It is currently considered to be the go to method for transforming the raw data.

Peter_SSs · Saturday at 12:25 AM

If you want to stick with FILTERs ...
If you do not already have the TRIMRANGE function you might choose to wait a bit longer before deciding to change as this function allows for expanding ranges.
There is also a new "Trim ref" notation that allows for the abbreviation of the TRIMRANGE function, at least in some circumstances.

In the mini sheet below I have a standard FILTER function in G2, one using whole column references in G12, one using TRIMRANGE M2 and one using the trim ref abbreviations in M12. As you can see, they all produce the same result.

ajm.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Data1

Data2

Data3

Data4

Data5

2

U

N

E

D

I

C

G

R

B

C

G

R

B

3

T

N

H

N

F

C

X

I

B

C

X

I

B

4

C

G

R

B

C

O

H

S

B

C

O

H

S

B

5

F

E

O

U

I

6

P

E

N

O

U

7

A

Y

X

L

J

8

B

Y

H

J

U

9

C

X

I

B

10

Y

G

C

V

N

11

M

R

G

Y

P

12

K

F

T

K

S

C

G

R

B

C

G

R

B

13

G

X

E

A

X

C

X

I

B

C

X

I

B

14

M

A

W

L

G

C

O

H

S

B

C

O

H

S

B

15

B

C

W

P

I

16

S

H

V

F

J

17

C

O

H

S

B

18

K

W

O

U

A

19

C

O

I

E

D

20

Y

U

G

T

R

21

Sheet1

Cell Formulas
Range	Formula
G2:K4	G2	=FILTER(A2:E20,(A2:A20="C")*(E2:E20="B"))
M2:Q4	M2	=FILTER(TRIMRANGE(A:E,2),(TRIMRANGE(A:A,2)="C")*(TRIMRANGE(E:E,2)="B"))
G12:K14	G12	=FILTER(A:E,(A:A="C")*(E:E="B"))
M12:Q14	M12	=FILTER(A:.E,(A:.A="C")*(E:.E="B"))
Dynamic array formulas.

Now, if I add some more data the G2 formula would need amendment as you described, but the others all produce the correct results for the expanded data. The difference is that the two formulas in column M do not have the inefficiency (slowness) of the whole-column-ref formula in G12 and calculate just as quickly as if the range was specifically set to go to row 23.
TRIMRANGE and Trim refs have a few options about trimming the range(s) but I have just used the option to trim off unused rows at the bottom of the data.

ajm.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Data1

Data2

Data3

Data4

Data5

2

U

N

E

D

I

C

G

R

B

C

G

R

B

3

T

N

H

N

F

C

X

I

B

C

X

I

B

4

C

G

R

B

C

O

H

S

B

C

O

H

S

B

5

F

E

O

U

I

C

A

B

6

P

E

N

O

U

C

I

P

F

B

7

A

Y

X

L

J

8

B

Y

H

J

U

9

C

X

I

B

10

Y

G

C

V

N

11

M

R

G

Y

P

12

K

F

T

K

S

C

G

R

B

C

G

R

B

13

G

X

E

A

X

C

X

I

B

C

X

I

B

14

M

A

W

L

G

C

O

H

S

B

C

O

H

S

B

15

B

C

W

P

I

C

A

B

C

A

B

16

S

H

V

F

J

C

I

P

F

B

C

I

P

F

B

17

C

O

H

S

B

18

K

W

O

U

A

19

C

O

I

E

D

20

Y

U

G

T

R

21

C

A

B

22

X

L

H

Y

G

23

C

I

P

F

B

24

Sheet1

Cell Formulas
Range	Formula
G2:K4	G2	=FILTER(A2:E20,(A2:A20="C")*(E2:E20="B"))
M2:Q6	M2	=FILTER(TRIMRANGE(A:E,2),(TRIMRANGE(A:A,2)="C")*(TRIMRANGE(E:E,2)="B"))
G12:K16	G12	=FILTER(A:E,(A:A="C")*(E:E="B"))
M12:Q16	M12	=FILTER(A:.E,(A:.A="C")*(E:.E="B"))
Dynamic array formulas.

Alex Blakenburg · Saturday at 12:42 AM

@Peter_SSs, you might want to adjust the 2 TrimRange formulas to only Trim the whole range once and then use Index or ChooseCols to select the columns.
The current way of evaluating them independently means that if one of the columns drops short of the other column the formula will error out.
eg use something like.

Excel Formula:

=LET(rng,A:.E,
     FILTER(rng,(CHOOSECOLS(rng,1)="C")*(CHOOSECOLS(rng,5)="B")))

Peter_SSs · Saturday at 1:59 AM

Alex Blakenburg said:
@Peter_SSs, you might want to adjust ..

Hi Alex
Yes, I had thought of that and had drafted similar to your but with TAKE rather than CHOOSECOLS but not knowing what the OPs data or filters are and knowing that my columns were uniform, I decided to go for what I thought was a simpler formula to focus on the TRIMRANGE and Trim Refs concepts that I had introduced.

ajm · Monday at 12:44 AM

Peter_SSs said:
Hi Alex
Yes, I had thought of that and had drafted similar to your but with TAKE rather than CHOOSECOLS but not knowing what the OPs data or filters are and knowing that my columns were uniform, I decided to go for what I thought was a simpler formula to focus on the TRIMRANGE and Trim Refs concepts that I had introduced.

Thank you both. does TRIMRANGE work to remove unused rows at the end in the same way as creating a dynamic named range? That is, it limits the used range to just that with data?

Peter_SSs · Monday at 3:04 AM

ajm said:
Thank you both.

You're welcome, but three people have offered suggestions.

ajm said:
does TRIMRANGE work to remove unused rows at the end in the same way as creating a dynamic named range? That is, it limits the used range to just that with data?

More or less. You can read more about it & see examples here.

ajm · Monday at 8:38 PM

sorry Vincent. I completely missed your response. Thank you also.

coulombevin · Monday at 8:51 PM

ajm said:
sorry Vincent. I completely missed your response. Thank you also.

I did not provide anything but your welcome :P

replace filter

ajm

Well-known Member

Excel Facts

coulombevin

Board Regular

Alex Blakenburg

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Alex Blakenburg

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

ajm

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

ajm

Well-known Member

coulombevin

Board Regular

Similar threads

Forum statistics

Share this page

replace filter

Well-known Member

Excel Facts

Board Regular

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Board Regular

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