# Extracting Top 10 Customers from Huge Amount of Data

1. ## Extracting Top 10 Customers from Huge Amount of Data

The project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.

The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.

If anyone has any ideas on how I can accomplish this, I would be most grateful.

2. ## Re: Extracting Top 10 Customers from Huge Amount of Data

if your data is flat...you can use a pivot table...this will allow you to choose a top 10 (you don't NEED a PT for top 10)....but once you create your list you can double click the total of each customer and excel automatically creates a new worksheet with the breakdown and totals....can you upload a sample worksheet?

3. ## Re: Extracting Top 10 Customers from Huge Amount of Data

You need to use a Pivot Table.

http://www.excelfunctions.net/Excel-...-Tutorial.html

They have built in functions to do just what you are after.

Where a customer has more than one account. Set them up as Regions. You will understand when you access the tutorial.

4. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Hi and welcome to the forum

1st, create a unique list of customers,,,an easy way to do this is top copy the entire list somewhere, and then use "remove duplicates" (Data/data Tools)

Once you have that list, (say in column d1) use =sumif(range,D1) copied down This will give the total for each customer

then use =rank() based on that list

5. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Originally Posted by judgeh59
if your data is flat...you can use a pivot table...this will allow you to choose a top 10 (you don't NEED a PT for top 10)....but once you create your list you can double click the total of each customer and excel automatically creates a new worksheet with the breakdown and totals....can you upload a sample worksheet?
Here is a sample worksheet. The data is a 3-month snapshot as opposed to 12 months.

a report.xls

6. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Ernest, Thank you for your input. I will look into a pivot table. There is another avenue I am now exploring, but pivot table are now on my radar.

I know not where to add an * but consider it added!!

7. ## Re: Extracting Top 10 Customers from Huge Amount of Data

lower left of the post....thanks and I'll take a look

8. ## Re: Extracting Top 10 Customers from Huge Amount of Data

I have the file but it has to headers....I can see Col B is Dates and Col G is a Price....do I need to worry about Col A,C,D,E?....thanks

9. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Sorry About that....I need to know which field is customer number?....

10. ## Re: Extracting Top 10 Customers from Huge Amount of Data

The first field is the customer number.

11. ## Re: Extracting Top 10 Customers from Huge Amount of Data

so this isn't a completed file...it's just gives the basics....I took some liberties on the column names....the pivot table was created with customer column that I generated by creating another column without the .XX....I use charges for the data and Date for the columns....then I grouped the columns by month....the Cust 110050 worksheet was created by double clicking on the total in the pivot table it creates a new worksheet that gives you the detail.....HTH

12. ## Re: Extracting Top 10 Customers from Huge Amount of Data

The only info you need to worry about is the customer number and the \$ amount. The rest of the information is internal use data.

13. ## Re: Extracting Top 10 Customers from Huge Amount of Data

did you read post # 4?

14. ## Re: Extracting Top 10 Customers from Huge Amount of Data

I did. I appreciate your idea. I am in the process of experimenting with the idea. Thank you.

15. ## Re: Extracting Top 10 Customers from Huge Amount of Data

The pivot table looks good. Is it possible to sort it highest to lowest, while maintaining the integrity of the entire line? Probably a dumb question.......I tried it and it just sort on just the total column.......

16. ## Re: Extracting Top 10 Customers from Huge Amount of Data

yes....you should have been able to rightclick in any the total fields and there is sort choice....I should have done that in the 1st place....I forgot

17. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Here is updated file....I also had to delete a bit of data because of file size....

18. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Originally Posted by judgeh59
Here is updated file....I also had to delete a bit of data because of file size....
Thank you!!

19. ## Re: Extracting Top 10 Customers from Huge Amount of Data

you are welcome....

20. ## Re: Extracting Top 10 Customers from Huge Amount of Data

A PT will only sort based on the 1st column

21. ## Re: Extracting Top 10 Customers from Huge Amount of Data

I'm not seeing that....I can right click on any the 6 columns in my pivot table and sort by that column....

22. ## Re: Extracting Top 10 Customers from Huge Amount of Data

ok, may be a new feature in 2010

unless you are manually sortingthe table, in which case, the sorting will need to be done again when the table is refreshed

23. ## Re: Extracting Top 10 Customers from Huge Amount of Data

excellent Point Sir....she is running 2007....Sorry Susan but this may not work for you....thanks FDibbins....

24. ## Re: Extracting Top 10 Customers from Huge Amount of Data

Copy column A to a new sheet. Select the data, click on the Data tab, click on remove duplicates. Copy this list of unique values and paste into column M. (choose a different column if you like but change the reference in the following formula). In column N enter this formula:

Formula:
`Please Login or Register  to view this content.`
and copy down the length of the data in column M. Select columns M and N and sort on column N in Largest to smallest order. The top ten customers by purchase will be the first 10 in the listing after sorting.

25. ## Re: Extracting Top 10 Customers from Huge Amount of Data

newdoverman, this was already suggested in post #4

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1