Create A List From A List
 

I have a List of values in Three Columns and countless rows.
Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 5 3/4 1 1/2
Row3 7 3/8 2
Row4 21 3/8 1 1/2
Row5 6 3/4 1 1/2
Row6 0 "" ""
Row7 5 3/8 1 1/2

I need to create a list that combines the cells that are the same in Col's B
& C and Totals.

Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 11 3/4 1 1/2
Row3 7 3/8 2
Row4 26 3/8 1 1/2

I am kinda new to using Excel and don't know a lot about it but I am trying
to learn...
Thanks, In advance

Create A List From A List
 

Can you just create a pivot table from your data? Click a single cell in
your table, then Data Pivot Table. Follow the prompts... when you get to
Layout, drag Diameter then Length into the Row fields and Qty into the Data
area (be sure it says 'Sum of Qty'; if not, double-click on 'Count of Qty'
and select Sum). After it's finished, you may want to clean it up a bit:
right-click on the first diameter's subtotal (eg: 3/8 Total), select Field
Settings and choose 'None' under subtotals.

One caveat with Pivots tables: they won't automatically change when the
underlying data is updated. To update the pivot table, right-click in the
pivot table and select Refresh Data.

"BillyBoy" wrote:

I have a List of values in Three Columns and countless rows.
Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 5 3/4 1 1/2
Row3 7 3/8 2
Row4 21 3/8 1 1/2
Row5 6 3/4 1 1/2
Row6 0 "" ""
Row7 5 3/8 1 1/2

I need to create a list that combines the cells that are the same in Col's B
& C and Totals.

Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 11 3/4 1 1/2
Row3 7 3/8 2
Row4 26 3/8 1 1/2

I am kinda new to using Excel and don't know a lot about it but I am trying
to learn...
Thanks, In advance

Create A List From A List
 

Is there a way to make it work without using the pivot table?

"bpeltzer" wrote:

Can you just create a pivot table from your data? Click a single cell in
your table, then Data Pivot Table. Follow the prompts... when you get to
Layout, drag Diameter then Length into the Row fields and Qty into the Data
area (be sure it says 'Sum of Qty'; if not, double-click on 'Count of Qty'
and select Sum). After it's finished, you may want to clean it up a bit:
right-click on the first diameter's subtotal (eg: 3/8 Total), select Field
Settings and choose 'None' under subtotals.

One caveat with Pivots tables: they won't automatically change when the
underlying data is updated. To update the pivot table, right-click in the
pivot table and select Refresh Data.

"BillyBoy" wrote:

I have a List of values in Three Columns and countless rows.
Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 5 3/4 1 1/2
Row3 7 3/8 2
Row4 21 3/8 1 1/2
Row5 6 3/4 1 1/2
Row6 0 "" ""
Row7 5 3/8 1 1/2

I need to create a list that combines the cells that are the same in Col's B
& C and Totals.

Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 11 3/4 1 1/2
Row3 7 3/8 2
Row4 26 3/8 1 1/2

I am kinda new to using Excel and don't know a lot about it but I am trying
to learn...
Thanks, In advance

Create A List From A List
 

Two other means come to mind: Subtotals and DSUM functions.

For subtotals, you would first sort your data (in the sort dialog, sort by
Diameter then Length). Then, from the menu bar, Data Subtotals. Set the
dialog to have it subtotal at each change in length, using the Sum operation
on the Qty field. Then do another subtotal: Data Subtotals. This time,
at each change in diameter, Sum the Qty field, BUT ensure that 'replace
current subtotals' is not checked.

For the DSUM function, you would first have to create the table of all
possible diameter / length combinations (essentially, just the first table
again with the quantities left blank and the duplicates deleted). Suppose
that table is in A1:C600 of Sheet2 (and your original table is in A1:C2000 of
Sheet1. In Sheet2!A2, you would enter =DSUM(Sheet1!A:C,$A1,$B$1:$C2). In A3
enter =DSUM(Sheet1!A:C,$A1,$B$!:$C3)-SUM($A1:A1). Autofill that formula
through the remainder of column A. (Note that it's important that the
header, row 1, be identical in both tables).

"CADManBill" wrote:

Is there a way to make it work without using the pivot table?

"bpeltzer" wrote:

Can you just create a pivot table from your data? Click a single cell in
your table, then Data Pivot Table. Follow the prompts... when you get to
Layout, drag Diameter then Length into the Row fields and Qty into the Data
area (be sure it says 'Sum of Qty'; if not, double-click on 'Count of Qty'
and select Sum). After it's finished, you may want to clean it up a bit:
right-click on the first diameter's subtotal (eg: 3/8 Total), select Field
Settings and choose 'None' under subtotals.

One caveat with Pivots tables: they won't automatically change when the
underlying data is updated. To update the pivot table, right-click in the
pivot table and select Refresh Data.

"BillyBoy" wrote:

I have a List of values in Three Columns and countless rows.
Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 5 3/4 1 1/2
Row3 7 3/8 2
Row4 21 3/8 1 1/2
Row5 6 3/4 1 1/2
Row6 0 "" ""
Row7 5 3/8 1 1/2

I need to create a list that combines the cells that are the same in Col's B
& C and Totals.

Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 11 3/4 1 1/2
Row3 7 3/8 2
Row4 26 3/8 1 1/2

I am kinda new to using Excel and don't know a lot about it but I am trying
to learn...
Thanks, In advance

Create A List From A List
 

Ok,
I really appreciate your sussgestions. The problem with subtotals and
DSUM is that the table in my description are sorted based on the information
contained in other parts of the sheet. Changing the Sort would cause other
problems.

I know I could simply make a table with the dia.'s and len.'s and use SUMIF
to get the Qnty.'s. I was trying to make a More "Automated" sheet/book. I
believe that I am trying to "Over Do It".

Once again, Much Thanks

"bpeltzer" wrote:

Two other means come to mind: Subtotals and DSUM functions.

For subtotals, you would first sort your data (in the sort dialog, sort by
Diameter then Length). Then, from the menu bar, Data Subtotals. Set the
dialog to have it subtotal at each change in length, using the Sum operation
on the Qty field. Then do another subtotal: Data Subtotals. This time,
at each change in diameter, Sum the Qty field, BUT ensure that 'replace
current subtotals' is not checked.

For the DSUM function, you would first have to create the table of all
possible diameter / length combinations (essentially, just the first table
again with the quantities left blank and the duplicates deleted). Suppose
that table is in A1:C600 of Sheet2 (and your original table is in A1:C2000 of
Sheet1. In Sheet2!A2, you would enter =DSUM(Sheet1!A:C,$A1,$B$1:$C2). In A3
enter =DSUM(Sheet1!A:C,$A1,$B$!:$C3)-SUM($A1:A1). Autofill that formula
through the remainder of column A. (Note that it's important that the
header, row 1, be identical in both tables).

"CADManBill" wrote:

Is there a way to make it work without using the pivot table?

"bpeltzer" wrote:

Can you just create a pivot table from your data? Click a single cell in
your table, then Data Pivot Table. Follow the prompts... when you get to
Layout, drag Diameter then Length into the Row fields and Qty into the Data
area (be sure it says 'Sum of Qty'; if not, double-click on 'Count of Qty'
and select Sum). After it's finished, you may want to clean it up a bit:
right-click on the first diameter's subtotal (eg: 3/8 Total), select Field
Settings and choose 'None' under subtotals.

One caveat with Pivots tables: they won't automatically change when the
underlying data is updated. To update the pivot table, right-click in the
pivot table and select Refresh Data.

"BillyBoy" wrote:

I have a List of values in Three Columns and countless rows.
Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 5 3/4 1 1/2
Row3 7 3/8 2
Row4 21 3/8 1 1/2
Row5 6 3/4 1 1/2
Row6 0 "" ""
Row7 5 3/8 1 1/2

I need to create a list that combines the cells that are the same in Col's B
& C and Totals.

Example:
Col A(QNTY) Col B(Dia.) Col C(Length)
Row1 10 3/4 2
Row2 11 3/4 1 1/2
Row3 7 3/8 2
Row4 26 3/8 1 1/2

I am kinda new to using Excel and don't know a lot about it but I am trying
to learn...
Thanks, In advance