Subtotals didn't quite work...but a good idea....

here's what my data looks like:

PO# line no Description Quantity BOX # Quantity Boxes
555-00 2 11-4040-605NL 220 159-169 11
555-00 2 11-4040-605NL 100 170-174 5
555-00 2 11-4040-605NL 500 175-199 25
555-00 2 11-4040-605NL 20 200 1
555-00 3 11-4040-618 160 201-208 8
555-00 3 11-4040-618 100 209-213 5
555-00 3 11-4040-618 20 214 1
555-00 3 11-4040-618 20 215 1
555-00 3 11-4040-618 240 216-227 12
555-00 3 11-4040-618 20 228 1
555-00 3 11-4040-618 20 229 1
555-00 3 11-4040-618 40 230-231 2
555-00 3 11-4040-618 20 232 1
555-00 4 60-2517-618 400 233-237 5
555-00 4 60-2517-618 160 238-239 2
555-00 4 60-2517-618 80 240 1

I need to subtotal the quantity column. The subtotal wizard isn't finding
the quantity to subtotal. (it's returning zeroes for changes in the item #
column)

"Ron Rosenfeld" wrote:

On Wed, 26 Mar 2008 13:20:02 -0700, Mike Morris <Mike
wrote:

Here's my situation: I get a packing list in an excel spreadsheet that ties
directly back to one or more purchase order(s).

The spreadsheet has 4 columns from L to R: PO#, line #, Item #, quantity
shipped
where "line #" indicates the matching line for a given PO #.

the problem is that a single "line #" may be spread across many lines on the
excel sheet. (e.g. 500 pieces shipped may appear as individual entries of
125, 25, 25, 67, 38, 122, 98)

What I'd like to do is to "collapse" multiple lines where the value for "po#
and "line #" match (item # would then also match) and total the values for
"qty shipped" into a single line with the total # shipped.

(does all that make sense without a concrete example? I hope so...)


Any suggestions? Is this doable easily? (I'm doing it by hand on paper and
it's annoying)

Most likely the Subtotals wizard would work. I believe it is on the Data menu.

First, though, sort your data by PO#, then Line#

Then do your Subtotals wizard selecting for each change in Item#, total the
Quantity Shipped.

--ron