This is sometimes referred to as a “pivot” operation.
Modern approach
Several new functions in the current version of Excel make this task easier.
This formulaspillsthe four unique group names into the range E4:H4.
With the groups in place, we now have what we need to retrieve the names in each group.
As the formula is copied across, the reference to E5 isrelativeand changes at each new row.
The result is all names in each group are together in the same column.
However, Excel formulas won’t currently return anarray-of-arraysso this doesn’t work.
It does this by testing the group in cell E4 against all values in the named rangegroup.
When the result is TRUE, the IF function returns the row number (see next step).
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 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.
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.
ROW Function
The Excel ROW function returns the row number for a reference.
For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.
When no reference is provided, ROW returns the row number of the cell which contains the formula.
ROWS Function
The Excel ROWS function returns the count of rows in a given reference.
