Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unusual function needed, unusre how to/if possible
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unusual function needed, unusre how to/if possible
Hi Mike,
On Mar 26, 12:20 pm, Mike Morris <Mike wrote: 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) When this happens are the PO# and item # repeated? If so, you could use a pivot table. 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. Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unusual function needed, unusre how to/if possible
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unusual function needed, unusre how to/if possible
Does your data match what you sent previously? I don't see an item #
field in your post. Subtotals wizard works for me. 11-4040-605NL Total 840 11-4040-618 Total 640 60-2517-618 Total 640 Grand Total 2120 Dan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
unusual function needed, unusre how to/if possible
On Thu, 27 Mar 2008 13:50:00 -0700, Mike Morris
wrote: 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) Then your data is not what it appears to be. First of all, I don't see any "item #" column in what you've posted, so I don't really know what you are doing. BUT, when I take your data, and use the Subtotal wizard, looking to sum the Quantity on changes in "line no", it seems to work fine (see below). So far as not totaling the Quantity, most likely those values are TEXT rather than numbers. Subtotal will not total numbers that are stored as text. You can test this by seeing the result of the formula =ISTEXT(cell_ref) where cell_ref is some cell that contains what you think is a Quantity. Here is what I get with your data from above: ----------------------- PO# line no Description Quantity BOX # 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 2 Total 840 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 3 Total 640 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 4 Total 640 Grand Total 2120 ----------------------------------- --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a worksheet function to create an unusual cell reference pat | Excel Worksheet Functions | |||
TEXT function with unusual results | Excel Discussion (Misc queries) | |||
Unusual Request... | Excel Discussion (Misc queries) | |||
User-Defined Function Unusual Operation, Again | Excel Programming | |||
Unusual Operation of a User Defined Function | Excel Programming |