How do I compress table to aggregate values for unique names?

MikeMcCollister

Board Regular
Joined
May 6, 2020
Messages
75
Office Version
  1. 365
Platform
  1. Windows
I have a "Regular Table" with names and numbers associated with them. I want to compress that table into the alphabetized unique names with the aggregate values for each name. I can use

Excel Formula:
=SORT(UNIQUE(Table1[Name]))

to get the sorted array for unique names. However, I don't know how to get the aggregate values for each name so that it creates an array that will grow if my original table grows. Any idea how to do that?

1740512388559.png


In the screen capture above, I put the formula that I am using in B15 as text in C15 for reference. B16 to B20 use similar formulas.

Thanks,

Mike
 
How about
Excel Formula:
=GROUPBY(Table1{Name],Table1[Number],0,0)

Thanks. That is exactly what I needed. I did have to make a minor revision, as follows:

Excel Formula:
=GROUPBY(Table1[Name], Table1[Number], SUM, 0, 0)

With the sort, it is this:

Excel Formula:
=SORT(GROUPBY(Table1[Name], Table1[Number], SUM, 0, 0))
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top