GROUPBY
Syntax
Description
This function creates groups based on a key (a column) selected by the user. These groups are then used to sort the results of either aggregate or group series functions. It is possible to create sub-groups by using this function again on another key from the same workbook sheet. The rows for each group series are highlighted when the GROUPBY function is complete.
Example
Given the following data:
| Customer_ | Name | Item |
|---|---|---|
| 2535 | Jon | Zamioculcas |
| 2888 | Mike | Spider Lily |
| 2535 | Jon | Datura |
| 2535 | Jon | Dahlia |
| 8654 | <null> | Windflower |
| 5788 | Jon | Snapdragon |
| 2888 | Mike | Marguerite |
| 2535 | Jon | Windflower |
| 2888 | Mike | Elephant's Ear |
| 2535 | Jon | Chinese Evergreen |
| 5788 | Jon | Begonia |
| 5788 | Jon | Starfish Plant |
| 2535 | Jon | Hare's Foot Fern |
| 2535 | Jon | Venus Flytrap |
| 3545 | Monique | Red Flame Ivy |
| 8654 | <null> | Starfish Plant |
| 4587 | <null> | Begonia |
Use GROUPBY(#RawData!Name) to create initial groups.
| Customer |
|---|
| <null> |
| Jon |
| Mike |
| Monique |
Use GROUPBY(#RawData!Customer_) to create sub-groups.
| Customer | Customer_Number |
|---|---|
| <null> | 4587 |
| <null> | 8654 |
| Jon | 2535 |
| Jon | 5788 |
| Mike | 2888 |
| Monique | 3545 |
Use a further group series or aggregation function to look at our results more closely (here use GROUPCOUNT to find the total number of orders per customer).
| Customer | Customer_Number | Num_Orders |
|---|---|---|
| <null> | 4587 | 1 |
| <null> | 8654 | 2 |
| Jon | 2535 | 7 |
| Jon | 5788 | 3 |
| Mike | 2888 | 3 |
| Monique | 3545 | 1 |
Also see:
- GROUPBYBIN To group values into bins.
- GROUPBYGAP To group by gaps in a timestamp.