User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Return Unique List of values from a Single Column, where Multiple Occurrences 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

 

 

Unique List
Unique List - Single Column

"Open Image in New Tab" for a full and clear view. 

 

 

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 - Cell B2 formula (non-Array formula):

 

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 a bit shorter / Array formula.

 

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.