Looking for a function to list out the data in a column in sortedorder.

[hr.seeho]

Hi All,
I have some problem programming the excel table i have to sum up a
huge list of numbers.
We have many Equipment and they will be name item 1, item 2.... (These
name are not fixed and they will be added to the table we we go
along.)

For Each equipment we have area. We are suppose to sum up all the Area
for each different type of equipment.
I figure using sumif function we can sum up all the area for each
equipmnt. But that requires me to first know what are the exact names
for each equipment.

Is there any function we can automatically list out the equipment in
sorted order.

Source :
Equipment Area
Item 1 50
Item 1 50
Item 1 50
Item 2 150
Item 2 150
Item 2 150
Item 1 50
Item 1 50
Item 1 50
Item 3 100
Item 3 100
Item 1 100
Item 3 50
Item 3 100
Item 3 100

Output :
Equipment Area
Item 1 400
Item 2 450
Item 3 450

 

[Andrea Jones]

Use the Advanced Filter to get a list of unique values, see the bottom of
this page http://www.allaboutoffice.co.uk/exfilter.htm for details. You can
then sort the resulting list and create your SUMIF functions.

Andrea Jones

 

[Dave Peterson]

You could add headers to your data, then sort the data and use Data|Subtotal to
get those totals.

Or you may want to consider using a pivottable.
You can a row of headers to all the columns (if you don't have them).

Then (in xl2003 menus):
Data|Pivottable
follow the wizard until you get to a dialog with Layout on it.
Click that Layout button
Drag the header for Equipment into the row field.
Then drag the header for Area into the Data field.
(If it doesn't say "Sum of...", then double click on it and change it to Sum.)

Then finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[hr.seeho]

You could add headers to your data, then sort the data and use Data|Subtotal to
get those totals.

Or you may want to consider using a pivottable.
You can a row of headers to all the columns (if you don't have them).

Then (in xl2003 menus):
Data|Pivottable
follow the wizard until you get to a dialog with Layout on it.
Click that Layout button
Drag the header for Equipment into the row field.
Then drag the header for Area into the Data field.
(If it doesn't say "Sum of...", then double click on it and change it to Sum.)

Then finish up the wizard.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):http://office.microsoft.com/downloa....com/assistance/2002/articles/xlconPT101.aspx











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks. The Pivot table solves the problem.