Home |
Search |
Today's Posts |
#1
|
|||
|
|||
PLEASE HELP ! SORT & SUMMARIZE ?
Column A Is A Group Of Lengths, Many Are The Same Length Column B Indicates Components Of That Length Column C Indicates Different Components Of That Length A B C 8 2 1 7 2 0 8 3 1 6 2 2 8 1 1 7 1 1 I'd Like To enter data in Columns A ,B,C. I would like to have Column E display all the lengths from Column A (only once) in assending order. I would like Column F to represent the sum of all component for that length from Column B and Column G to represent the sum of all components for that length from Column C. Can this be written in a formula of some type with NO MANUAL functions so Columns E, F, G will automatically come out in this format? E F G 6 2 2 7 3 1 8 6 3 Any Help Would Be Appreciated. Thanks, Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
#2
|
|||
|
|||
Seems like a good situation for a Pivot Table With your data arranged as follows in cells A1:C7 Len Comp1 Comp2 8 2 1 7 2 0 8 3 1 6 2 2 8 1 1 7 1 1 Select that range DataPivot Table Excel List..[Next] Select the range...[Next] Select Existing Worksheet, select a cell to place the pivot table...[Next] Click [Layout] and drag labels to... ROW: Len DATA: Comp1, Comp2 Click [OK] then [Finish] Now: Click and hold on the Data column label and drag it on top of the Total column label....then let go. The resulting table will now look like this: Len_____Sum of Comp1_____Sum of Comp2 6______________2_________2 7______________3_________1 8______________6_________3 Grand Total_____11_________6 Does that help? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
#3
|
|||
|
|||
Ron, That gave me the results I need, but is there a way to make a pivot table stay active so every time you change the data entered, it would make the changes to the final table? I'll be entering new data constantly and trying to have the results without doing manual operations. Thanks for your help. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
#4
|
|||
|
|||
Options: 1)If the data range is going to be expanding, you''ll need to make sure the source range for the Pivot Table extends below the anticipated end of the data. You may also want to set the Pivot Table to hide blanks. 2)Since the Pivot Table won't refresh automatically with data changes (by design), you'll need to refresh it on demand. Something you can work with? Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
#5
|
|||
|
|||
Thanks again Ron, Is there another way to accomplish this without manually acting on the data? Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
#6
|
|||
|
|||
Yes (with VBA and event-driven code)...but...Is right clicking on the Pivot Table and selecting "Refresh" really too much work? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
#7
|
|||
|
|||
Thanks Ron, I'm trying to prepare this for another user. The idea is to enter the data, have it formulated (which they won't see) and have it applied to a report form which can be printed. I need to get the data in this format to apply to form. Your help is appreciated. Travelersway -- travelersway ------------------------------------------------------------------------ travelersway's Profile: http://www.excelforum.com/member.php...o&userid=17623 View this thread: http://www.excelforum.com/showthread...hreadid=389628 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Sort Ascending button - Excel 2003 | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |