I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

Without messing with programming you can use an AutoFilter. You can then
move these if you want.

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"cminor" wrote in message
...
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8
Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns
10
and 11, I have the item name and price. Elsewhere in the worksheet, I
need
to list all items that have a total quantity greater than zero along with
the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with
zero
quantities. Where the nonzero items will eventually be listed is a page
to
be printed and I don't need to waste paper.

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

BACKUP your spreadsheet and test this on a COPY of it.

I would create a new worksheet and manually format it to look pretty.
Then I would run this macro.

You need to change the names of the two worksheets in the code to suit.

If you have any beefs with the following code then post back.

Public Sub setupPrint()
' This Sub copies the grid that is in wsFrom to wsTo
' but only the rows that do not have zeroes.
'
' Set rF to be the Cell address of the first row after the header
' Set rT to be the first cell that you want data in the printout
'
' Asssumes that all rows have item names


Dim wb As Workbook
Dim wsFrom As Worksheet, wsTo As Worksheet
Dim rF As Range, rT As Range

Set wb = ActiveWorkbook
Set wsFrom = wb.Sheets("The name of the sheet with the grid")
Set wsTo = wb.Sheets("Your printout sheet name")

Set rF = wsFrom.Range("A2") ' first cell after the header
Set rT = wsTo.Range("A2") ' first cell with data

Do While Not rF.Offset(0, 9).Value = ""
If Not rF.Offset(0, 8) = 0 Then
wsFrom.Range(rF.Address & ":" & rF.Offset(0,
10).Address).Copy
rT.PasteSpecial xlPasteValues
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set wsTo = Nothing
Set wsFrom = Nothing
Set wb = Nothing
End Sub

Jason Lepack wrote:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

Jason,

Your first illustration is exactly right, but what I want the output to look
like is this:
ITEM QTY PRICE
Item 2 2.1 .11
Item 4 4.11 .13
Item 5 3.12 .14

The output from the table data is to be elsewhere on the worksheet and won't
show the categories. There is already other data there from other
calculation and this data will follow in line with the previous.

Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

Thanks.

"Jason Lepack" wrote:

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

cminor wrote:
Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

I'm not sure what you are saying. Are you saying that you can't use a
macro? If that is not what you are saying then try this macro instead.
I think you will like it better.

Public Sub setupPrint()
' This Sub creates a new list of item, qty, price
' for items that don't have a zero qty.
'
' Set rF to be the cell with the first item name.
' Set rT to be the top left cell of the new list.
'
' Asssumes that all rows have item names

Dim ws As Worksheet
Dim rF As Range, rT As Range

Set ws = ActiveWorkbook.ActiveSheet
' *** set this to point at the ITEM column in the old list
Set rF = ws.Range("J2")
' *** set this to point at the ITEM column in the old list

' *** change this to be wherever you want the new list
Set rT = ws.Range("O1")
' *** change this to be wherever you want the new list

rT.Value = "ITEM"
rT.Offset(0, 1).Value = "QTY"
rT.Offset(0, 2).Value = "PRICE"

Set rT = rT.Offset(1, 0)
Do While Not rF.Value = ""
If Not rF.Offset(0, -1) = 0 Then
rT.Value = rF.Value
rT.Offset(0, 1).Value = rF.Offset(0, -1).Value
rT.Offset(0, 2).Value = rF.Offset(0, 1).Value
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set ws = Nothing
End Sub

cminor wrote:
Jason,

Your first illustration is exactly right, but what I want the output to look
like is this:
ITEM QTY PRICE
Item 2 2.1 .11
Item 4 4.11 .13
Item 5 3.12 .14

The output from the table data is to be elsewhere on the worksheet and won't
show the categories. There is already other data there from other
calculation and this data will follow in line with the previous.

Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

Thanks.

"Jason Lepack" wrote:

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

John,

I can't use a filter because it isn't automatic. The data I am dealing with
are in a spreadsheet that will be used over and over again. It can't contain
anything that requires action beyond input into a series of forms.

"John Bundy" wrote:

Without messing with programming you can use an AutoFilter. You can then
move these if you want.

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"cminor" wrote in message
...
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8
Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns
10
and 11, I have the item name and price. Elsewhere in the worksheet, I
need
to list all items that have a total quantity greater than zero along with
the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with
zero
quantities. Where the nonzero items will eventually be listed is a page
to
be printed and I don't need to waste paper.

Jason,

Apparently, you wrote this before I responded to your first reply. There
are two problems with solving the problem with the macro. First, it would
have to be run after the input is filled in on a form. Second, the output
from your code includes the extraneous data from the "in" range.

I don't know if Excel has such thing as an "on enter, run macro", but
something like that would be needed for a macro to work. Macros run when the
workbook is opened (at least all mine do) so all of the fields would be zero
at that point. I guess it could run in the background the whole time the
workbook is opened so it would constantly update the data and eventually be
correct, but there must be something simpler.

"Jason Lepack" wrote:

BACKUP your spreadsheet and test this on a COPY of it.

I would create a new worksheet and manually format it to look pretty.
Then I would run this macro.

You need to change the names of the two worksheets in the code to suit.

If you have any beefs with the following code then post back.

Public Sub setupPrint()
' This Sub copies the grid that is in wsFrom to wsTo
' but only the rows that do not have zeroes.
'
' Set rF to be the Cell address of the first row after the header
' Set rT to be the first cell that you want data in the printout
'
' Asssumes that all rows have item names


Dim wb As Workbook
Dim wsFrom As Worksheet, wsTo As Worksheet
Dim rF As Range, rT As Range

Set wb = ActiveWorkbook
Set wsFrom = wb.Sheets("The name of the sheet with the grid")
Set wsTo = wb.Sheets("Your printout sheet name")

Set rF = wsFrom.Range("A2") ' first cell after the header
Set rT = wsTo.Range("A2") ' first cell with data

Do While Not rF.Offset(0, 9).Value = ""
If Not rF.Offset(0, 8) = 0 Then
wsFrom.Range(rF.Address & ":" & rF.Offset(0,
10).Address).Copy
rT.PasteSpecial xlPasteValues
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set wsTo = Nothing
Set wsFrom = Nothing
Set wb = Nothing
End Sub

Jason Lepack wrote:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

Let me tell you what it is I am doing. Maybe I am going about it all in the
wrong way.

I am writing a take-off program (spreadsheet) to calculate the materials,
labor and subs needed to build a house. I am up to the point of framing
materials and working on floor joists. The problem is that there are a near
infinite number of possible joist sizes (2X6, 2X8, 2X10, etc.), spans, and
centers in any given floor. Therefore, I created a matrix of the
possibilities I expect to encounter. The matrix is filled in based on the
input data (span, joist size, and center). I now need to reduce the matrix
to only the josts size, including the correct length (2X8-10', 2X10-12',
etc.), quantities, and price.

Many years ago, I wrote all this in Basica (on my Tandy!) and it works
great. However, Basica won't run on modern computers and I don't have time
to update my programming skills. In code, this all so much simpler. In a
spreadsheet, I have to work within single cells with single formulae.

"cminor" wrote:

I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

Jason,

This sorta worked but with three problems. First, it listed the column
headings in the first line of output. I know I showed that in my
illustration, but I have a header for that already. Second, it only listed
the last item, quantity, and price. There are eight total in my test sheet.

Third, if I go back to my input sheet and change the input, I have to run
the macro again to update the output. This is the bigger problem that I
referred to about automation. I thought I cold give the macro a user-defined
function name and reference the name in the cell I want to start the output
in, but I can't find a means of doing that.

A little more help perhaps?

"Jason Lepack" wrote:

cminor wrote:
Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

I'm not sure what you are saying. Are you saying that you can't use a
macro? If that is not what you are saying then try this macro instead.
I think you will like it better.

Public Sub setupPrint()
' This Sub creates a new list of item, qty, price
' for items that don't have a zero qty.
'
' Set rF to be the cell with the first item name.
' Set rT to be the top left cell of the new list.
'
' Asssumes that all rows have item names

Dim ws As Worksheet
Dim rF As Range, rT As Range

Set ws = ActiveWorkbook.ActiveSheet
' *** set this to point at the ITEM column in the old list
Set rF = ws.Range("J2")
' *** set this to point at the ITEM column in the old list

' *** change this to be wherever you want the new list
Set rT = ws.Range("O1")
' *** change this to be wherever you want the new list

rT.Value = "ITEM"
rT.Offset(0, 1).Value = "QTY"
rT.Offset(0, 2).Value = "PRICE"

Set rT = rT.Offset(1, 0)
Do While Not rF.Value = ""
If Not rF.Offset(0, -1) = 0 Then
rT.Value = rF.Value
rT.Offset(0, 1).Value = rF.Offset(0, -1).Value
rT.Offset(0, 2).Value = rF.Offset(0, 1).Value
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set ws = Nothing
End Sub

cminor wrote:
Jason,

Your first illustration is exactly right, but what I want the output to look
like is this:
ITEM QTY PRICE
Item 2 2.1 .11
Item 4 4.11 .13
Item 5 3.12 .14

The output from the table data is to be elsewhere on the worksheet and won't
show the categories. There is already other data there from other
calculation and this data will follow in line with the previous.

Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

Thanks.

"Jason Lepack" wrote:

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

I can solve the part about runnign the macro on worksheet changes.

change the opening to

Private Sub Worksheet_Change(ByVal Target As Range)

this will run thecode any time the worksheet changes

"cminor" wrote:

Jason,

This sorta worked but with three problems. First, it listed the column
headings in the first line of output. I know I showed that in my
illustration, but I have a header for that already. Second, it only listed
the last item, quantity, and price. There are eight total in my test sheet.

Third, if I go back to my input sheet and change the input, I have to run
the macro again to update the output. This is the bigger problem that I
referred to about automation. I thought I cold give the macro a user-defined
function name and reference the name in the cell I want to start the output
in, but I can't find a means of doing that.

A little more help perhaps?

"Jason Lepack" wrote:

cminor wrote:
Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

I'm not sure what you are saying. Are you saying that you can't use a
macro? If that is not what you are saying then try this macro instead.
I think you will like it better.

Public Sub setupPrint()
' This Sub creates a new list of item, qty, price
' for items that don't have a zero qty.
'
' Set rF to be the cell with the first item name.
' Set rT to be the top left cell of the new list.
'
' Asssumes that all rows have item names

Dim ws As Worksheet
Dim rF As Range, rT As Range

Set ws = ActiveWorkbook.ActiveSheet
' *** set this to point at the ITEM column in the old list
Set rF = ws.Range("J2")
' *** set this to point at the ITEM column in the old list

' *** change this to be wherever you want the new list
Set rT = ws.Range("O1")
' *** change this to be wherever you want the new list

rT.Value = "ITEM"
rT.Offset(0, 1).Value = "QTY"
rT.Offset(0, 2).Value = "PRICE"

Set rT = rT.Offset(1, 0)
Do While Not rF.Value = ""
If Not rF.Offset(0, -1) = 0 Then
rT.Value = rF.Value
rT.Offset(0, 1).Value = rF.Offset(0, -1).Value
rT.Offset(0, 2).Value = rF.Offset(0, 1).Value
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set ws = Nothing
End Sub

cminor wrote:
Jason,

Your first illustration is exactly right, but what I want the output to look
like is this:
ITEM QTY PRICE
Item 2 2.1 .11
Item 4 4.11 .13
Item 5 3.12 .14

The output from the table data is to be elsewhere on the worksheet and won't
show the categories. There is already other data there from other
calculation and this data will follow in line with the previous.

Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

Thanks.

"Jason Lepack" wrote:

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

Sean,

That's close, but there will be hundreds of changes in the worksheet that
are unrelated to the macro. Is there a way to make it run only whne specific
cells change?

"Sean Timmons" wrote:

I can solve the part about runnign the macro on worksheet changes.

change the opening to

Private Sub Worksheet_Change(ByVal Target As Range)

this will run thecode any time the worksheet changes

"cminor" wrote:

Jason,

This sorta worked but with three problems. First, it listed the column
headings in the first line of output. I know I showed that in my
illustration, but I have a header for that already. Second, it only listed
the last item, quantity, and price. There are eight total in my test sheet.

Third, if I go back to my input sheet and change the input, I have to run
the macro again to update the output. This is the bigger problem that I
referred to about automation. I thought I cold give the macro a user-defined
function name and reference the name in the cell I want to start the output
in, but I can't find a means of doing that.

A little more help perhaps?

"Jason Lepack" wrote:

cminor wrote:
Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

I'm not sure what you are saying. Are you saying that you can't use a
macro? If that is not what you are saying then try this macro instead.
I think you will like it better.

Public Sub setupPrint()
' This Sub creates a new list of item, qty, price
' for items that don't have a zero qty.
'
' Set rF to be the cell with the first item name.
' Set rT to be the top left cell of the new list.
'
' Asssumes that all rows have item names

Dim ws As Worksheet
Dim rF As Range, rT As Range

Set ws = ActiveWorkbook.ActiveSheet
' *** set this to point at the ITEM column in the old list
Set rF = ws.Range("J2")
' *** set this to point at the ITEM column in the old list

' *** change this to be wherever you want the new list
Set rT = ws.Range("O1")
' *** change this to be wherever you want the new list

rT.Value = "ITEM"
rT.Offset(0, 1).Value = "QTY"
rT.Offset(0, 2).Value = "PRICE"

Set rT = rT.Offset(1, 0)
Do While Not rF.Value = ""
If Not rF.Offset(0, -1) = 0 Then
rT.Value = rF.Value
rT.Offset(0, 1).Value = rF.Offset(0, -1).Value
rT.Offset(0, 2).Value = rF.Offset(0, 1).Value
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set ws = Nothing
End Sub

cminor wrote:
Jason,

Your first illustration is exactly right, but what I want the output to look
like is this:
ITEM QTY PRICE
Item 2 2.1 .11
Item 4 4.11 .13
Item 5 3.12 .14

The output from the table data is to be elsewhere on the worksheet and won't
show the categories. There is already other data there from other
calculation and this data will follow in line with the previous.

Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

Thanks.

"Jason Lepack" wrote:

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.

This is my last try for now. I have about a 45 min commute.

I added in the "Worksheet_Change part.

If this doesn't work, then you could email me a copy of your
spreadsheet the way you want it to look on output, because if this
isn't it, then I don't understand what it is that you want.

Cheers,
Jason Lepack

Private Sub Worksheet_Change(ByVal Target As Range)
' This Sub creates a new list of item, qty, price
' for items that don't have a zero qty.
'
' Set rF to be the cell with the first item name.
' Set rT to be the top left cell of the new list.
'
' Asssumes that all rows have item names

Dim ws As Worksheet
Dim rF As Range, rT As Range

' ** if you decide that you want more rows or columns then change
this
Set rF = Intersect(Target, Range("A2:H31"))
' ** if you decide that you want more rows or columns then change
this

' skips out if the cell that changed is not in the specified range
If Not rF Is Nothing Then
Set ws = ActiveWorkbook.ActiveSheet

' *** set this to point at the ITEM column in the old list
Set rF = ws.Range("J2")
' *** set this to point at the ITEM column in the old list

' *** change this to be wherever you want the new list
Set rT = ws.Range("O2")
' *** change this to be wherever you want the new list

Do While Not rF.Value = ""
If Not rF.Offset(0, -1) = 0 Then
rT.Value = rF.Value
rT.Offset(0, 1).Value = rF.Offset(0, -1).Value
rT.Offset(0, 2).Value = rF.Offset(0, 1).Value
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Do While Not rT.Row = 32
rT.Value = ""
rT.Offset(0, 1).Value = ""
rT.Offset(0, 2).Value = ""
Set rT = rT.Offset(1, 0)
Loop
End If
Set rT = Nothing
Set rF = Nothing
Set ws = Nothing
End Sub



cminor wrote:
Sean,

That's close, but there will be hundreds of changes in the worksheet that
are unrelated to the macro. Is there a way to make it run only whne specific
cells change?

"Sean Timmons" wrote:

I can solve the part about runnign the macro on worksheet changes.

change the opening to

Private Sub Worksheet_Change(ByVal Target As Range)

this will run thecode any time the worksheet changes

"cminor" wrote:

Jason,

This sorta worked but with three problems. First, it listed the column
headings in the first line of output. I know I showed that in my
illustration, but I have a header for that already. Second, it only listed
the last item, quantity, and price. There are eight total in my test sheet.

Third, if I go back to my input sheet and change the input, I have to run
the macro again to update the output. This is the bigger problem that I
referred to about automation. I thought I cold give the macro a user-defined
function name and reference the name in the cell I want to start the output
in, but I can't find a means of doing that.

A little more help perhaps?

"Jason Lepack" wrote:

cminor wrote:
Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

I'm not sure what you are saying. Are you saying that you can't use a
macro? If that is not what you are saying then try this macro instead.
I think you will like it better.

Public Sub setupPrint()
' This Sub creates a new list of item, qty, price
' for items that don't have a zero qty.
'
' Set rF to be the cell with the first item name.
' Set rT to be the top left cell of the new list.
'
' Asssumes that all rows have item names

Dim ws As Worksheet
Dim rF As Range, rT As Range

Set ws = ActiveWorkbook.ActiveSheet
' *** set this to point at the ITEM column in the old list
Set rF = ws.Range("J2")
' *** set this to point at the ITEM column in the old list

' *** change this to be wherever you want the new list
Set rT = ws.Range("O1")
' *** change this to be wherever you want the new list

rT.Value = "ITEM"
rT.Offset(0, 1).Value = "QTY"
rT.Offset(0, 2).Value = "PRICE"

Set rT = rT.Offset(1, 0)
Do While Not rF.Value = ""
If Not rF.Offset(0, -1) = 0 Then
rT.Value = rF.Value
rT.Offset(0, 1).Value = rF.Offset(0, -1).Value
rT.Offset(0, 2).Value = rF.Offset(0, 1).Value
Set rT = rT.Offset(1, 0)
End If
Set rF = rF.Offset(1, 0)
Loop
Set rT = Nothing
Set rF = Nothing
Set ws = Nothing
End Sub

cminor wrote:
Jason,

Your first illustration is exactly right, but what I want the output to look
like is this:
ITEM QTY PRICE
Item 2 2.1 .11
Item 4 4.11 .13
Item 5 3.12 .14

The output from the table data is to be elsewhere on the worksheet and won't
show the categories. There is already other data there from other
calculation and this data will follow in line with the previous.

Also, whatever method is used can't be cut and paste or anything that is not
automatic. The worksheet this is listed on is only for printing. Input is
done on another worksheet.

Thanks.

"Jason Lepack" wrote:

Cat5 Cat6 Cat7 Cat8 Total Item Name Price
0 Item1 0.1
2 2 Item2 0.11
0 Item3 0.12
2 2 Item4 0.13
3 3 Item5 0.14
0 Item6 0.15

Limited by the fact that I can only fit this many fields on the google
screen.

In this example you would want your output to look like this:
Cat5 Cat6 Cat7 Cat8 Total Item Name Price
2 2.1 Item2 0.11
2 4.11 Item4 0.13
3 3.12 Item5 0.14

Correct?

cminor wrote:
I am trying to create a list of data based on nonblank or nonzero cells in
another list. More specific: I have a group of cells, 30 Rows by 8 Columns,
which contain data (numeric) that represent quantities of 30 items from 8
categories. Each category is then totalled in the 9th column. In columns 10
and 11, I have the item name and price. Elsewhere in the worksheet, I need
to list all items that have a total quantity greater than zero along with the
item name and price. I expect this to be 6 or 8 items out of the 30.

What I am trying to avoid is listing all 30 items and having some with zero
quantities. Where the nonzero items will eventually be listed is a page to
be printed and I don't need to waste paper.