Taking the Zeros out of an array when displaying

[Bullytt]

HI I am using a spreadsheet over several sheets to show a statement of account.

I can use:{=IF(Order!C2:C23="n",Order!B2:B23,0)} to produce an array that
checks column C for an "n" and displays the date (unpaid invoice date as it
happens) from column B. A zero is inserted if there is no "n"

I would like to know if there is a way to ONLY display the non-zero cells:
12/12/09
14/12/09 etc rather than

0
0
12/12/09
0
0
0
14/12/09

That is showing at the moment.

Many thanks in advance for any helpful suggestions - bit of a novice in over
my head!

Bullytt

 

[T. Valko]

{=IF(Order!C2:C23="n",Order!B2:B23,0)}

What is the *exact* location of that formula?

 

[Bullytt]

HI - and thanks for a response.

The formula is for the cell range B19:B32 (used ctrl+shift+enter)
This range is on a sheet called "Statement" which is why I have to go to
sheet "Order" to fetch the data.

Hope that makes sense!

Bullytt

 

[T. Valko]

The formula is for the cell range B19:B32

Enter this array formula** in B19:

=IF(ROWS(B$19:B19)>COUNTIF(Order!C$2:C$23,"N"),"",INDEX(Order!B:B,SMALL(IF(Order!C$2:C$23="N",ROW(Order!C$2:C$23)),ROWS(B$19:B19))))

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Bullytt]

Superb stuff! - I managed to work out enough to use the same formula for
different columns in "Order".

Would this formula be the best place to add another condition?

There is an input in c16 on the same sheet for a customer number - this
number (to be matched against Order column D) should filter the results again
(the first filter being whether there is an "n" in "Order column C"

Once again many thanks for the first formula - I certainly would never be
able to create a solution like that!

Regards,

Bullytt

 

[T. Valko]

We can use the same basic formula with some minor tweaks.

Since there are now 2 conditions let's use a cell that will count how many
records meet the conditions.

Enter this formula in B18:

=SUMPRODUCT(--(Order!C2:C23="N"),--(Order!D223=C16))

Then, the array formula in B19:

=IF(ROWS(B$19:B19)>B$18,"",INDEX(Order!B:B,SMALL(IF(Order!C$2:C$23="N",IF(Order!D$2$23=C$16,ROW(Order!C$2:C$23))),ROWS(B$19:B19))))

 

[Bullytt]

Absolutely perfect!

Huge thanks

Bullytt

We can use the same basic formula with some minor tweaks.

Since there are now 2 conditions let's use a cell that will count how many
records meet the conditions.

Enter this formula in B18:

=SUMPRODUCT(--(Order!C2:C23="N"),--(Order!D223=C16))

Then, the array formula in B19:

=IF(ROWS(B$19:B19)>B$18,"",INDEX(Order!B:B,SMALL(IF(Order!C$2:C$23="N",IF(Order!D$2$23=C$16,ROW(Order!C$2:C$23))),ROWS(B$19:B19))))

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!