Puzzeling Subtotals.....

  • Thread starter Thread starter Wayne
  • Start date Start date
W

Wayne

I'm extracting data from AutoCAD to produce a parts list
simular to the one shown below.:-

Product_Code Qty Description Unit_Price Unit_Weight_(Kg)
101 1 Lamp $20.00 3
101 1 Lamp $20.00 3
101 1 Lamp $20.00 3
101 1 Lamp $20.00 3
502 1 Table $105.00 20
502 1 Table $105.00 20
502 1 Table $105.00 20
752 1 Chair $36.00 10
101 1 Lamp $20.00 3
101 1 Lamp $20.00 3
752 1 Chair $36.00 10
752 1 Chair $36.00 10
752 1 Chair $36.00 10


using the subtotal function produces various views of the
data but non are in a required format of.:


Product_Code Qty Description Unit_Price Unit_Weight_
(KgTotal_Price Total_Weight
101 7 Lamp $20.00 3 $140.00 21
502 3 Table $105.00 20 $315.00 60
752 4 Chair $36.00 10 $144.00 40
Total 14 $599.00 121

This can be acheived "manually" but can become painfull
when a parts list is produced with over a 1,000 items,
many product groups and several lists produced per day...

Any Idea's gang!!!!


Thanks in advance.
 
How about a pretty quick manual way that gets really close?

Do your data|subtotals and subtotal by Product_code. Include the sum for qty,
unit_price and unit_weight.

Now use the outlining symbols on the left to show just the subtotal rows.
Select C1 to the grand total row (or one row up).

Then Edit|goto|special|Visible cells
type = and then type the previous row number.
(like =C18 if you're typing in C19)

then hit ctrl-enter to fill the selection.

Now select column A and
Edit|replace
Find what: (spacebar)Total
replace with: (leave blank)

and then fix up that last grand total row if you have to.
 
Back
Top