Ranking???

[Cinny]

Hi

I have the following data in three separate columns

Column a is the customer
column B is the different products that a customer might use
Column C is the revenue received for each product

Each customer can have up to 20 rows of data which would look something like
this:

Row 1 : Julies Computers Column A, Hard Drive Column B, $3000 Column C
Row 2: Julies Computers Column A, Flat Screen Column B, $4000 Column C

I need to rank or find out which product for each customer has the maxiumn
revenue, for example in the above the Flat screen would be ranked number 1 in
column D and the Hard Drive would be ranked number 2 in column D. I have
about 70000 rows of data.

Could you advise what the forumla would be for this?

Thanks in advance.

 

[Max]

With such a large data range, suggest you try a pivot table

Some easy steps to lead you in (in xl2003)
Assume your col headers in A1:C1 are: Cust, Prod, Rev
Drag n drop Cust in ROW area
Double-click on it, set SubTotals to None > OK

Drag n drop Prod in ROW area (below Cust)
Drag n drop Rev in DATA area (it'll appear as Sum of Rev)
Click OK > Finish

Go to the pivot sheet (to the left)
Select a cell under Prod
Then click on the PivotTable drop down* > choose Sort and Top 10
*in the PivotTable Toolbar

In the dialog, under AutoSort options:
Select Descending
Using Field: Sum of Rev
Click OK

The above will autosort the pivot table to your requirements
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik

 

[Max]

2 missing lines in the earlier steps,
re-instated, it should read as:

Assume your col headers in A1:C1 are: Cust, Prod, Rev

Select any cell within the table, click Data > Pivot Table ..
Click Next > Next. In step 3 of the wiz., click Layout

Drag n drop Cust in ROW area
Double-click on it, set SubTotals to None > OK

Drag n drop Prod in ROW area (below Cust)
Drag n drop Rev in DATA area (it'll appear as Sum of Rev)
Click OK > Finish

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---