eliminating zero-value rows

  • Thread starter Thread starter Scot B
  • Start date Start date
S

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.
 
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.
 
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)
 
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.
 
Back
Top