Filter Function - Blank cells return as 0

[FrankieGTH]

Hello everyone,

I'm using the Filter function on a table that has blank cells. When I use the function, blank cells return as 0. Ideally, I'd like the 0s not to be there and the filter returns blank cells as blank.

In an epic fail, I tried to use the substitute function, substituting "0" for "", however this chops off 0s from the hours column (30 becomes 3, etc). It feels that should be a way to do this, but my brain is failing. Would anyone be able to conjure up a formula to replace these 0s? I'd like to avoid using conditional formatting if possible.

Book1
	A	B	C	D	E	F	G	H	I
1	Staff	Department	Fire Trained	Hours Per Week		Pura	childrenswear	No	17
2	Pura	childrenswear	No	17		Leora	childrenswear	Yes	25
3	Leora	childrenswear	Yes	25		Audrey	childrenswear	0	0
4	Audrey	childrenswear				Marti	childrenswear	Yes	22
5	Gwenda	womenswear	No	10		Maryetta	childrenswear	Yes	18
6	Janee	menswear	No	28		Salome	childrenswear	0	20
7	Warren	menswear	No	17		Regenia	childrenswear	Yes	26
8	Shantel	menswear	No	20		Yung	childrenswear	0	30
9	Rachel	menswear	Yes	29
10	Marti	childrenswear	Yes	22		Pura	childrenswear	No	17
11	Maryetta	childrenswear	Yes	18		Leora	childrenswear	Yes	25
12	Branda	womenswear	Yes	19		Audrey	childrenswear
13	Boyd	menswear	No	26		Marti	childrenswear	Yes	22
14	Vada	menswear	Yes	29		Maryetta	childrenswear	Yes	18
15	Lowell	menswear	No	25		Salome	childrenswear		2
16	Kristina	menswear	Yes	14		Regenia	childrenswear	Yes	26
17	Becki	womenswear	Yes	36		Yung	childrenswear		3
18	Salome	childrenswear		20
19	Loura	womenswear	No	26
20	Regenia	childrenswear	Yes	26
21	Yung	childrenswear		30
Sheet1

Cell Formulas
Range	Formula
F1:I8	F1	=FILTER($A$1:$D$21,$B$1:$B$21="childrenswear")
F10:I17	F10	=SUBSTITUTE(FILTER($A$1:$D$21,$B$1:$B$21="childrenswear"),"0","")
Dynamic array formulas.

Sincerest thanks.

 

[jasonb75]

If you have the LET function

=LET(f,FILTER($A$1:$D$21,$B$1:$B$21="childrenswear"),IF(f=0,"",f))

If you don't have it,

=IF(FILTER($A$1:$D$21,$B$1:$B$21="childrenswear")=0,"",FILTER($A$1:$D$21,$B$1:$B$21="childrenswear"))

Effectively 2 versions of the same formula, it's just slightly more efficient using LET.

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Staff	Department	Fire Trained	Hours Per Week		Pura	childrenswear	No	17
2	Pura	childrenswear	No	17		Leora	childrenswear	Yes	25
3	Leora	childrenswear	Yes	25		Audrey	childrenswear
4	Audrey	childrenswear				Marti	childrenswear	Yes	22
5	Gwenda	womenswear	No	10		Maryetta	childrenswear	Yes	18
6	Janee	menswear	No	28		Salome	childrenswear		20
7	Warren	menswear	No	17		Regenia	childrenswear	Yes	26
8	Shantel	menswear	No	20		Yung	childrenswear		30
9	Rachel	menswear	Yes	29
10	Marti	childrenswear	Yes	22
11	Maryetta	childrenswear	Yes	18
12	Branda	womenswear	Yes	19
13	Boyd	menswear	No	26
14	Vada	menswear	Yes	29
15	Lowell	menswear	No	25
16	Kristina	menswear	Yes	14
17	Becki	womenswear	Yes	36
18	Salome	childrenswear		20
19	Loura	womenswear	No	26
20	Regenia	childrenswear	Yes	26
21	Yung	childrenswear		30
22
Main

Cell Formulas
Range	Formula
F1:I8	F1	=FILTER(IF(ISNUMBER($A$1:$D$21),A1:D21,A1:D21&""),$B$1:$B$21="childrenswear")
Dynamic array formulas.

 

[FrankieGTH]

Thank you both very much indeed. @Fluff's answer I can understand, I really need to get to grips with the LET function @jasonb75 as it seems very useful.

Wishing you a peaceful weekend.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jasonb75]

I really need to get to grips with the LET function @jasonb75 as it seems very useful.

It is very useful, and very simple once you understand how it works. If you understand basic vba, think of LET as being the formula equivalent to variables.

In the longer version of my suggested formula the FILTER function is used twice with both instances being identical. Because it is used twice it has to be calculated twice, even though we know that the results will be the same both times.

Using LET to assign the FILTER section of the formula to a name / variable means that it only needs to be calculated once because we can use the name / variable to refer to the results of the first calculation rather than performing the calculation each time.

Not much difference here as it is only one extra calculation on a small array, but with a large array that is repeated 4 or 5 times in a single formula there can be a big improvement in calculation time.