**Return Unique List of values from a Single Column - Multiple Occurrences to Appear only Once**

**Related Links:**

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

**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.

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

**Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas**.