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

Unique List – Single Column

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…).