eliminating zero-value rows

[Scot B]

Greetings,

A series of rows comprise a product list we are using (eg "Item X, Item Y,
Item Z"). A column next to these products is used to designate the quantity
of each item we will need for a particular job. So, we might put a "3" in
the column next to Item X, or a "0" next to Item Y.

I would like to then have a tab that lists all the materials we need for a
job, and their quantities, without having the blank rows if the quantity is
zero.

Any ideas how to accomplish this?

Thanks for your help.

Cheers,

Scot B.

 

[Dave Peterson]

I'd keep all my data in place and apply Data|filter|autofilter.

Then filter to show just the cells that are greater than 0.

 

[Max]

Just an option to play around with ..

Suppose you have
in Sheet1, cols A and B,
data from row2 down

Prod Qty
Item1 1
Item2 0
Item3 3
Item4 2
etc

Put in C2: =IF(B2="","",IF(B2<>0,ROW(),""))

Copy C2 down by a safe max expected number of rows
say, down to C100?

In a new Sheet2
----------------------
With the same labels in A1:B1, : Prod Qty

Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)-1,COLUMN(A1)-1))

Copy A2 across to B2, then fill down by as many rows as
was done in col C of Sheet1, viz. to B100

Cols A and B will return only those rows from Sheet1
without "zeros" under "Qty"

For the sample data in Sheet1,
it'll show in Sheet2 as:

Prod Qty
Item1 1
Item3 3
Item4 2

(with Item2 removed)

 

[Max]

Looks like the option worked for the OP, re: note received ..
---
From: "Scot Begovich" ..
To: (e-mail address removed)
Subject: Re: eliminating zero-value rows
Date: Thu, 14 Oct 2004 17:10:20 -0500

Thanks for your help!

This worked perfectly and was a spectacular response.

Cheers,

Scot B.