Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for a function to list out the data in a column in sortedorder.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for a function to list out the data in a column in sorted
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 " wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for a function to list out the data in a column in sortedorder.
On Apr 22, 7:57*pm, Dave Peterson wrote:
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/download...lconPT101.aspx wrote: 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 -- Dave Peterson- Hide quoted text - - Show quoted text - Thanks. The Pivot table solves the problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to retrieve a list of unique characters from a column | Excel Worksheet Functions | |||
building list from a column of data | Excel Worksheet Functions | |||
How to generate a function from the given data list(Y and X) | Excel Worksheet Functions | |||
Data in list w/same A:B move to column | Excel Worksheet Functions | |||
Capture and list data from a column or row | Excel Worksheet Functions |