|
Featured Tutorial
Featured Macro
Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula)
See how to:
1)Extract a unique list of records with a formula and display in a column using the functions IF, ROWS, INDEX, SMALL, FREQUENCY, MATCH, ROW, SUMPRODUCT and COUNTIF.
2)Extract a unique list of numbers only with a formula and display in a column with the extra function ISNUMBER.
3)Extract a unique list of words only with a formula and display in a column with the extra function ISTEXT.
4)Extract a unique list of records with a formula and display in a row (requires the additional function TRANSPOSE to deal with the fact that the FREQUENCY function delivers a vertical array and we need to have it orientated horizontally (in a row).
Also see this related videos:
Excel Magic Trick 362: 12 Amazing Counting Formulas
Excel Magic Trick 368: Count Unique Items With More Than One Criteria
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula
Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
Highline Excel Class 36: Array Formulas Basics
Aladin from Mr Excel Message Board
1)Extract a unique list of records with a formula and display in a column using the functions IF, ROWS, INDEX, SMALL, FREQUENCY, MATCH, ROW, SUMPRODUCT and COUNTIF.
2)Extract a unique list of numbers only with a formula and display in a column with the extra function ISNUMBER.
3)Extract a unique list of words only with a formula and display in a column with the extra function ISTEXT.
4)Extract a unique list of records with a formula and display in a row (requires the additional function TRANSPOSE to deal with the fact that the FREQUENCY function delivers a vertical array and we need to have it orientated horizontally (in a row).
Also see this related videos:
Excel Magic Trick 362: 12 Amazing Counting Formulas
Excel Magic Trick 368: Count Unique Items With More Than One Criteria
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula
Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
Highline Excel Class 36: Array Formulas Basics
Aladin from Mr Excel Message Board