Cut list macro suggestions

[jiml]

Hi all,

I have a worksheet which contains a list of dimensions in
rows 1 through 128. example:

A B C E F

Width Length Thickness Qty Total Length

1 2.5 12 1.25 4 48
2 4.5 16.5625 1 2 33.125
3 2.75 8.5 .75 1 8.5
4 2.5 18 1.25 4 72
5 4.5 20 1 3 60

What I need to do is create a list of every row that has
the same width AND thickness and sum the Total Length of
each of these. So the end result of this example would
look something like this:

2.5 120 1.25
4.5 93.125 1
2.75 8.5 .75

Has anyone ever done anything like this? Any input would
be GREATLY appreciated.

 

[Frank Kabel]

Hi
try using a pivot table for this:
- use width and length as row items
- use sum of thickness as data item
See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
for more information about this

For a formula approach you may use several SUMPRODUCT statements. e.g.
=SUMPRODUCT(-((A1:A100=2.5),--(B1:B100=120),C1:C100)

 

[kkknie]

You can use subtotals for this, but will have to create another column.
Here's the procedure:

Create a new column (say column G)
Put a formula in the column that combines width and thicknes
(=A1&"x"&C1")
Sort the data by the new column
Give the columns labels (if you already haven't
Under the Data Menu, select Subtotals and fill in
- At each change in you new column
- Use function Sum
- Add subtotals to Total