how to find the top 3 amount per customer?

**mackie8** · 03-20-2011, 09:24 AM

pls help me ...
my data is like this...

customer# mobile# usage amount
abc123 12345 $65.89
abc123 12346 $89.78
abc123 12458 $12.80
abc123 18903 $33.67
cba111 18907 $90.21
cba111 09876 $56.20
cba111 67854 $10.45
cba111 78912 $44.23

how can i find the top 3 mobile numbers with the highest usage per customer number...
btw im having more than 1000 rows of customers...

thanks....

**Armando Montes** · 03-20-2011, 01:38 PM

Try the rank function:
http://www.contextures.com/excel-functions-rank.html

**Roger Govier** · 03-20-2011, 02:22 PM

Hi

The easiest way is to create a Pivot Table
Instructions for XL2003

Mark your data range>Data>List>Create List> Click my List has Headers
Place cursor in List>Data>Pivot Table>Finish
Drag Customer# to the Row area
Drag Mobile# to the Row Area
Drag Usage to the Data area and ensure that it is set to Sum

Right click on Mobile#>Field Setting>Advanced>Autosort Options>Descending>using Field>Sum of Usage
Top10 Autoshow>on>show Top 3

for XL2007 and later
Place cursor within your data>Insert tab>Table>click my data has headers
Place cursor in Table>Design tab>Summarise with Pivot Table>OK
Drag Customer# to Row Label
Drag Mobile# to Row label (beneath Customer#)
Drag usage to Values area
Place cursor in Pivot Table>Design tab>Report Layout>Show in Outline form
Right click Mobile#>Sort>Descending by>Sum of Usage
Right click mobile#>Filter>Show>Top>3