User Rating: 5 / 5     Return Unique List of values from a Single Column - Multiple Occurrences to Appear only Once

Extract Unique List of Values from a Single Column

Column A values appear either once (Unique) or multiple times (Duplicate) - these are extracted to appear ONLY ONCE in a column;

In column B, the formula (non-array) is to be entered in cell B2 & copied down;

Note: If cell B2 formula is in a different column, replace "B\$1:B1" in the formula with that column (ex. if formula is in column F then update to "F\$1:F1"); if cell B2 formula starts from a different cell, say B16 instead of B2, then replace "B\$1:B1" with "B\$15:B15";

-----------------------------------------------------------------------------------------------

Extract Unique List from Column A

Cell B2 formula:

=IFERROR(INDEX(A\$2:A\$20, MATCH(0, INDEX( COUNTIF( B\$1:B1, A\$2:A\$20)+ (A\$2:A\$20="") ,), 0)), "")

Explanation - Enter formula (non-array) in cell B2 and copy down:

1) COUNTIF(B\$1:B1, A\$2:A\$20): counts values of column A in the preceding cells of the formula column (column B) as the formula gets copied down. It returns an array of 1s and zeros, with 1 indicating column A values which already occur in these preceding cells.

2) INDEX(COUNTIF(B\$1:B1,A\$2:A\$20)+ (A\$2:A\$20=""),): this combines the above with (A\$2:A\$20="") which returns TRUE for blank cells and this TRUE becomes 1 when used in calculation. Index is used to keep it a non-array formula. As this is copied down, it returns an array of 1s and zeros, where 1 indicates column A values which already occur in preceding cells or for blank cells. The aim is to match with zero values which return non-blank column A cells which have not already appeared in preceding cells of column B. This is the lookup_array argument of the MATCH function.

3) MATCH(0,INDEX( COUNTIF(B\$1:B1, A\$2:A\$20)+ (A\$2:A\$20=""),) ,0): The Match function is used with lookup_value of zero to determine column A values which do not appear in the preceding cells and this also skips blank cells, as explained above. Used with the INDEX function - "INDEX(A\$2:A\$20," - the unique column A values are returned.

-----------------------------------------------------------------------------------------------

Related Formulas - illustrated in the Ebook of Excel Formulas.

Column C: Returns a Unique List of Values by using a formula similar to column B, except that the column C formula is an Array formula and shorter.

Column D: Uses the FREQUENCY function to return a Unique List of Values and the formula is not dependent on previous cells of the column like column B / C formulas ie. cell D2 formula can be entered in any cell without requiring a change as mentioned in the abovementioned Note (If cell B2 formula is in a different column, replace "B\$1:B1" in the formula with that column...).