GROUPBY

Syntax

GROUPBY(a ny)

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.