+ Reply to Thread
Results 1 to 25 of 25

Extracting Top 10 Customers from Huge Amount of Data

  1. #1
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Smile 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.

    Thank you in advance for any advice, comments or ideas!


  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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?
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    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.
    Last edited by mehmetcik; 04-12-2013 at 04:04 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    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
    Last edited by FDibbins; 04-12-2013 at 08:43 PM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Extracting Top 10 Customers from Huge Amount of Data

    Quote Originally Posted by judgeh59 View Post
    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. #6
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    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. #7
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Extracting Top 10 Customers from Huge Amount of Data

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

  8. #8
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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. #9
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Extracting Top 10 Customers from Huge Amount of Data

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

  10. #10
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Extracting Top 10 Customers from Huge Amount of Data

    The first field is the customer number.

  11. #11
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    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. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    Re: Extracting Top 10 Customers from Huge Amount of Data

    did you read post # 4?

  14. #14
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    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. #15
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    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. #16
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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. #17
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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....
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    04-12-2013
    Location
    Colville WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Thumbs up Re: Extracting Top 10 Customers from Huge Amount of Data

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


  19. #19
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    Re: Extracting Top 10 Customers from Huge Amount of Data

    you are welcome....

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    Re: Extracting Top 10 Customers from Huge Amount of Data

    A PT will only sort based on the 1st column

  21. #21
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    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
    Last edited by FDibbins; 04-12-2013 at 05:57 PM.

  23. #23
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,308

    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. #24
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,265

    Re: Extracting Top 10 Customers from Huge Amount of Data

    newdoverman, this was already suggested in post #4

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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