This is sometimes referred to as a “pivot” operation.
Modern approach
Several new functions in the current version of Excel make this task easier.
As the formula is copied down column F, the reference to E5 isrelativeand changes at each new row.
As a final result, we have all names in each group together in the same row.
However, Excel formulas won’t currently return anarray-of-arraysso this won’t work.
The trick is that SMALL is working with an array that is already filtered by group.
It does this by testing the group in cell E5 against all values in the named rangegroup.
When the result is true, the IF function returns the row number.
This array goes into SMALL as thearrayargument.
The result is that the SMALL function returns the row number for each name in a given group.
This number is supplied to the INDEX function asrow_num, with the named rangenameas the array.
Finally, INDEX returns the name associated with each row number.
This happens after all names for a given group have been extracted.
To suppress this error, we wrap the formula in theIFERROR functionand return anempty string("").
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
from a set of numeric data.
IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
COLUMN Function
The Excel COLUMN function returns the column number for a reference.
For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.
When no reference is provided, COLUMN returns the column number of the cell which contains the formula.
COLUMNS Function
The Excel COLUMNS function returns the count of columns in a given reference.
