PLEASE HELP ! SORT & SUMMARIZE ?

  • Thread starter Thread starter travelersway
  • Start date Start date
T

travelersway

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
 
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
Data>Pivot 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,

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
 
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
 
Thanks again Ron,

Is there another way to accomplish this without manually acting on the
data?

Travelersway
 
Yes (with VBA and event-driven code)...but...Is right clicking on th
Pivot Table and selecting "Refresh" really too much work?

Regards,
Ro
 
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
 
Back
Top