Find highest sum in a group without pivot tables

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

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

5. 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!

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

7. 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. Re: Find highest sum in a group without pivot tables

Duplicate Post

9. Re: Find highest sum in a group without pivot tables

Top 20 on sheet Top 20

Kind regards
Leo

10. Re: Find highest sum in a group without pivot tables

Originally Posted by ThirdFret
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. Re: Find highest sum in a group without pivot tables

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

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