+ Reply to Thread
Results 1 to 11 of 11

Find highest sum in a group without pivot tables

  1. #1
    Registered User
    Join Date
    08-14-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    10

    Find highest sum in a group without pivot tables

    Hello!

    I have a file that contains the product SKU, Vendor Name and then page view count for each SKU (along with a ton of other metadata). For example:

    SKU Vendor Name Page Views
    B001 Vendor A 0
    B002 Vendor A 12
    B003 Vendor A 3
    B004 Vendor A 213
    B005 Vendor A 24
    B006 Vendor B 56
    B007 Vendor B 245
    B008 Vendor B 2456
    B009 Vendor B 765
    B010 Vendor C 23
    B011 Vendor C 78
    B012 Vendor C 34
    B013 Vendor D 14
    B014 Vendor D 47
    B015 Vendor D 24
    B016 Vendor D 462

    I'd like to create a list that tells me who the top 20 vendors with the highest total sum of page views are. In the example above the result would be:

    Vendor Name Sum of Page Views
    Vendor B 3522
    Vendor D 547
    Vendor A 252
    Vendor C 135

    Normally I would just use a Pivot table for this and it's done, but the file is gigantic and regularly updated through an ETL data connection which can change the list of vendors and products every week - and Pivot tables just seem to crash Excel when I refresh the data. Is there a way to create the list using a blend of formulas instead?

    Any help will be hugely appreciated, even if it's to confirm that Pivot tables are the only way! Thank you

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Find highest sum in a group without pivot tables

    My two cents - If the file is so large that Pivot tables crash when trying to update, having formulas to accomplish the same thing would be even more resource demanding, and would give you even more issues.
    i recommend looking into Powerpivot, a free excel add-in (for version 2010 and newer) that handles larger data sets.
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    07-28-2015
    Location
    Pruszków, Poland
    MS-Off Ver
    365 Business
    Posts
    58

    Re: Find highest sum in a group without pivot tables

    I think pivot table will be the best solution. You can change your data in a "table" by pushing CTR+t (when standing on the data). Then you can stop worrying about changing the list of vendors, only refresh pivot. Piovt tables is dedicated to large emount of data, so maybe somthing else crush Excel while refreshing the data?

    Attach sample of your data or whole file if u can.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,580

    Re: Find highest sum in a group without pivot tables

    I'll add my vote to the others. I often overlook pivot tables but I do know that the formulas to make this happenw with large amounts of data will slow you calculations to a crawl.
    ChemistB
    My 2˘

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    08-07-2015
    Location
    AB, Canada
    MS-Off Ver
    Excel 2013 x64
    Posts
    132

    Re: Find highest sum in a group without pivot tables

    I think I might have a solution here...

    If you put your data in cells A2:C19, then the following formula in cell G2 should be able to drag down column G. This formula is an array formula so must be entered by pressing Ctrl+SHIFT+Enter instead of just Enter.
    Please Login or Register  to view this content.
    Then the following formula in cell F2 can be copied down column F. This one is also an array formula and must be entered by pressing Ctrl+SHIFT+Enter.
    Please Login or Register  to view this content.
    I'll upload an example here in a minute. Let me know if this works for you.

    edit: Attached is an example file. This might produce the desired outcome without relying on a pivot table, but like some other posters have mentioned here, this array solution will likely run A LOT slower than using a pivot table. If you can deal with refreshing every once in a while, I'd suggest taking that route. Hope this helps, though!
    Attached Files Attached Files
    Last edited by ThirdFret; 09-08-2015 at 11:19 AM.

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Find highest sum in a group without pivot tables

    If PivotTables might crash excel, then a mess of complicated array functions will definitely hurt.

    If it's like this:
    External Data => Spreadsheet page => Pivot Table

    Can you cut out the middle step and just feed the external data directly to your pivot table?

    I agree that PowerPivot might be the way to go; you can set it up so it only grabs the metadata columns you need, instead of the whole table; if you only need 20% of the columns, then you can cut the data you need to grab to 20% just like that. If you need all the columns though, that wouldn't gain anything.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  7. #7
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Find highest sum in a group without pivot tables

    If you need all the columns though, that wouldn't gain anything.
    This isn't true. Ex. You made need to summarize all of the columns, but instead of showing x number of rows for vendor A, your output would only have one row for each vendor. The file would speed up considerably by utilizing PowerPivot.
    With PowerPivot, you could have over 1 Million rows (the limit in excel) in your source data, and still have the file run quite quickly.
    I can think of a few scenarios where the size of your output may cause issues, but that would be limited.

  8. #8
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Find highest sum in a group without pivot tables

    Duplicate Post

  9. #9
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: Find highest sum in a group without pivot tables

    Top 20 on sheet Top 20


    Kind regards
    Leo
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-14-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Find highest sum in a group without pivot tables

    Quote Originally Posted by ThirdFret View Post
    I think I might have a solution here...

    If you put your data in cells A2:C19, then the following formula in cell G2 should be able to drag down column G. This formula is an array formula so must be entered by pressing Ctrl+SHIFT+Enter instead of just Enter.
    Please Login or Register  to view this content.
    Then the following formula in cell F2 can be copied down column F. This one is also an array formula and must be entered by pressing Ctrl+SHIFT+Enter.
    Please Login or Register  to view this content.
    I'll upload an example here in a minute. Let me know if this works for you.

    edit: Attached is an example file. This might produce the desired outcome without relying on a pivot table, but like some other posters have mentioned here, this array solution will likely run A LOT slower than using a pivot table. If you can deal with refreshing every once in a while, I'd suggest taking that route. Hope this helps, though!
    Thank you ThirdFret! This worked perfectly :D

  11. #11
    Forum Contributor
    Join Date
    08-07-2015
    Location
    AB, Canada
    MS-Off Ver
    Excel 2013 x64
    Posts
    132

    Re: Find highest sum in a group without pivot tables

    Great! Glad I could help.

    Remember to mark the thread as SOLVED by going to the "Thread Tools" drop-down at the top of the thread!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 12-18-2012, 11:37 AM
  2. Find range of group & return highest value
    By Stiansen in forum Excel General
    Replies: 5
    Last Post: 02-10-2012, 04:02 AM
  3. Replies: 2
    Last Post: 09-19-2008, 10:22 AM
  4. how to find group highest values in excel
    By lol in forum Excel General
    Replies: 1
    Last Post: 06-19-2008, 12:43 AM
  5. Cannot Group Dates in Pivot Tables
    By jpgoeth in forum Excel General
    Replies: 2
    Last Post: 04-23-2007, 12:51 PM
  6. Pivot tables - Group Label on each line
    By DrIan in forum Excel General
    Replies: 5
    Last Post: 08-08-2006, 04:25 PM
  7. Group function while using Pivot tables
    By Siv in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-09-2005, 08:00 AM
  8. Won't allow me to group Pivot Tables
    By iwilkin in forum Excel General
    Replies: 1
    Last Post: 01-21-2005, 02:23 PM

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