Syntax
GROUPSELECT(<any>;<any>;<any>)
Description
Create columns with selected values using a key constant from a specified grouped column in the workbook.
Select a column from a worksheet and write in a constant from that column. Next, select the value column for which to compare the constant from the original column. The column being selected isn''t allowed to contain different values for the key constant. If there are different values for a key constant, an error results.
This is a excellent tool to create columns for use in a pivot table.
This is an aggregate function.
Example
Original Data:
Department | Year | Dollar |
|---|---|---|
| Department 1 | 2010 | 10 |
| Department 1 | 2010 | 20 |
| Department 1 | 2011 | 10 |
Department 2 | 2011 | 10 |
| Department 2 | 2012 | 10 |
| Department 1 | 2013 | 30 |
| Department 1 | 2013 | 30 |
| Department 2 | 2013 | 35 |
Create a new worksheet:
GROUPBY(#RawData!Department) GROUPSELECT(#RawData!Year;2010;#RawData!Dollar) GROUPSELECT(#RawData!Year;2011;#RawData!Dollar) GROUPSELECT(#RawData!Year;2012;#RawData!Dollar) GROUPSELECT(#RawData!Year;2013;#RawData!Dollar)
| Department | Dollar_in_2010 | Dollar_in_2011 | Dollar_in_2012 | Dollar_in_2013 |
|---|---|---|---|---|
| Department 1 | <error> | 10 | <empty> | 30 |
| Department 2 | 10 | 10 | 35 |