Summary of values on a sheet

[Nick]

Is there a way of getting Excel to scan a column of values
and produce a new column showing one instance of each
value found?

For example, on an expense report I'm trying to create
there are various job sites listed in chronological order.
Each job site has a code, such as 5-1544 or 7-7050.

On the next sheet there is a summary that totals the
expenses for each site (using the sumproduct function
kindly supplied the other day).

The Detail sheet may look like this:

5/1/03 $12.00 5-1599
5/1/03 $11.00 7-7200
5/2/03 $64.00 5-1599
5/3/03 $21.00 5-1697

The Summary sheet should display one entry for each site:

5-1599 $72.00
7-7200 $11.00
5-1697 $21.00

I can use SUMPRODUCT to calculate the value but I'd like
the Summary sheet to automatically fill in one cell for
each site listed on the detail sheet.

Is this possible?

Nick

 

[Paul Corrado]

That first message was wrong.

Correct menu selections are

Data|Filter|Advanced

PC

 

[Ken Wright]

Use Data / Filter / Advanced Filter, check 'Unique values only' and use the 'Choose another
location' option. This will give you a list of unique values, against which you can use
SUMPRODUCT to do all the totals.

Another option is to use Pivot tables, which will do just about anything you want bar making the
tea. A good intro to be found from Debra Dalgleish at the following link:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm