Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to Create a Conditional Drop down list | Excel Worksheet Functions | |||
How to create a Sub validation list in excel? | Excel Discussion (Misc queries) | |||
How do I create a command from a list box | Excel Discussion (Misc queries) | |||
How to create a macro that compares a list to another list | New Users to Excel | |||
create a drop down list with the source from a different workbook | Excel Discussion (Misc queries) |